/**
SWMMR Database Setup
These are T-SQL statements to build the database for support of SWMM5-based Hydrologic and Hydraulic (H&H) modeling
**/

USE [master]
GO
sp_configure 'xp_cmdshell', 1
GO
RECONFIGURE
GO

CREATE DATABASE [SWMMR]-- ON PRIMARY-- LOG ON 
GO
DECLARE @temp_dir nvarchar(100) 
SET @temp_dir = 'C:\SWMMR'
DECLARE @temp_dir_statement nvarchar(max)
SET @temp_dir_statement = 'EXEC master.dbo.xp_create_subdir ''' + @temp_dir + ''''
EXEC(@temp_dir_statement)
DECLARE @temp_dir_share_statement nvarchar(max)
SET @temp_dir_share_statement = 'EXEC master.dbo.xp_cmdshell ''net share SWMMR=' + @temp_dir + ' /UNLIMITED'''
EXEC(@temp_dir_share_statement)
GO

EXEC dbo.sp_dbcmptlevel @dbname=N'SWMMR', @new_cmptlevel=90 -- set compatible with SS2005
GO

IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')) BEGIN
    EXEC [SWMMR].[dbo].[sp_fulltext_database] @action = 'disable'
END
GO

ALTER DATABASE [SWMMR] SET ANSI_NULL_DEFAULT OFF -- columns must be explicitly defined to accept NULL values
GO
ALTER DATABASE [SWMMR] SET ANSI_NULLS OFF -- WHERE col = NULL is true for NULL values in col
GO
ALTER DATABASE [SWMMR] SET QUOTED_IDENTIFIER OFF -- may not use "" to specify nonstandard identifier name
GO
ALTER DATABASE [SWMMR] SET ALLOW_SNAPSHOT_ISOLATION ON -- turn on row versioning
GO
ALTER DATABASE [SWMMR] SET READ_COMMITTED_SNAPSHOT ON -- db engine uses statement-level row versioning by default
GO
ALTER DATABASE [SWMMR] SET CURSOR_CLOSE_ON_COMMIT OFF -- don't close all cursors on a connection when a commit or rollback transaction is issued 
GO
--SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 
--GO
ALTER DATABASE [SWMMR] SET ANSI_PADDING OFF -- don't pad unfilled char values with blanks
GO
ALTER DATABASE [SWMMR] SET ANSI_WARNINGS ON -- don't generate warnings when NULL values appear in aggregate functions
-- from msdn: SET ANSI_WARNINGS must be ON when you are creating or manipulating indexes on computed columns or indexed views. 
-- If SET ANSI_WARNINGS is OFF, CREATE, UPDATE, INSERT, and DELETE statements on tables with indexes on computed columns or indexed views will fail.
-- ANSI_WARNINGS should be set to ON for executing distributed queries. When ANSI_WARNINGS and ARITHABORT are both ON, queries terminate 
-- when an overflow or divide by zero error occurs
-- default: OFF
GO
ALTER DATABASE [SWMMR] SET ARITHABORT ON -- when overflow or divide-by-zero error occurs in a query, return NULL and raise a warning  
-- default: OFF
GO
ALTER DATABASE [SWMMR] SET AUTO_CLOSE OFF -- when ON, sqlserver releases system resources after last connection closes. 
-- Turned OFF to improve wait time for new connections.
GO
ALTER DATABASE [SWMMR] SET AUTO_CREATE_STATISTICS ON -- auto create stats for columns in query predicate to aide optimizer
GO
ALTER DATABASE [SWMMR] SET AUTO_SHRINK OFF -- don't reclaim unused space, has a tendency to fragment indexes
GO
ALTER DATABASE [SWMMR] SET AUTO_UPDATE_STATISTICS ON -- auto update column stats for optimizer
GO
ALTER DATABASE [SWMMR] SET CURSOR_DEFAULT  GLOBAL -- default to global scope for new cursor
GO
ALTER DATABASE [SWMMR] SET CONCAT_NULL_YIELDS_NULL OFF -- when concating to NULL, treat NULLs as ''
GO
ALTER DATABASE [SWMMR] SET NUMERIC_ROUNDABORT OFF -- losses in precision do not generate error messages
GO
ALTER DATABASE [SWMMR] SET RECURSIVE_TRIGGERS OFF -- disallow nested triggers
GO
ALTER DATABASE [SWMMR] SET ENABLE_BROKER -- service broker handles messaging
GO
ALTER DATABASE [SWMMR] SET AUTO_UPDATE_STATISTICS_ASYNC OFF -- always update stats before optimizing a query
GO
ALTER DATABASE [SWMMR] SET DATE_CORRELATION_OPTIMIZATION OFF -- don't correlate dtime columns for tables sharing a foreign key
GO
ALTER DATABASE [SWMMR] SET TRUSTWORTHY ON -- share server roles with WITH EXECUTE AS ...
GO
ALTER DATABASE [SWMMR] SET PARAMETERIZATION SIMPLE  -- auto-parameterize values in queries so future query execution plans can reuse previous execution plans
GO
ALTER DATABASE [SWMMR] SET READ_WRITE -- database is in read-write mod
GO
ALTER DATABASE [SWMMR] SET RECOVERY FULL -- fully log all transactions
GO
ALTER DATABASE [SWMMR] SET MULTI_USER -- allow multiple users, this is default
GO
ALTER DATABASE [SWMMR] SET PAGE_VERIFY CHECKSUM -- checksums are computed when each page is written and checked when read
GO
ALTER DATABASE [SWMMR] SET DB_CHAINING OFF -- db can't participate in cross-database ownership chaining 
GO

USE [SWMMR]
GO
CREATE SCHEMA Framework AUTHORIZATION [dbo]
GO

CREATE TABLE Framework.SchemaVersion (
	TIMESTAMP datetime DEFAULT GETDATE(),
	USERNAME [varchar](20) DEFAULT ORIGINAL_LOGIN(),
	VERSION [varchar](16),
    NOTES varchar(max) NULL
) ON [PRIMARY]
GO

IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N'modelking')
    DROP LOGIN [modelking]
GO
CREATE LOGIN [modelking] WITH PASSWORD = 'sunnyday', 
    DEFAULT_DATABASE = [SWMMR], 
    CHECK_EXPIRATION = OFF,
    CHECK_POLICY = OFF
CREATE USER [modelking] FOR LOGIN [modelking] 
    WITH DEFAULT_SCHEMA = [Framework]
GO
EXEC sp_addsrvrolemember @loginame = 'modelking', @rolename = 'bulkadmin'
GO
ALTER AUTHORIZATION ON SCHEMA::[Framework] TO [modelking]
GO

-- 0.2.4 full permissions to modelking
-- Database permissions
GRANT ALTER ANY APPLICATION ROLE TO [modelking] WITH GRANT OPTION
GRANT ALTER ANY ASSEMBLY TO [modelking] WITH GRANT OPTION
GRANT ALTER ANY ASYMMETRIC KEY TO [modelking] WITH GRANT OPTION
GRANT ALTER ANY CERTIFICATE TO [modelking] WITH GRANT OPTION
GRANT ALTER ANY CONTRACT TO [modelking] WITH GRANT OPTION
GRANT ALTER ANY DATABASE AUDIT TO [modelking] WITH GRANT OPTION
GRANT ALTER ANY DATABASE DDL TRIGGER TO [modelking] WITH GRANT OPTION
GRANT ALTER ANY DATABASE EVENT NOTIFICATION TO [modelking] WITH GRANT OPTION
GRANT ALTER ANY DATASPACE TO [modelking] WITH GRANT OPTION
GRANT ALTER ANY FULLTEXT CATALOG TO [modelking] WITH GRANT OPTION
GRANT ALTER ANY MESSAGE TYPE TO [modelking] WITH GRANT OPTION
GRANT ALTER ANY REMOTE SERVICE BINDING TO [modelking] WITH GRANT OPTION
GRANT ALTER ANY ROLE TO [modelking] WITH GRANT OPTION
GRANT ALTER ANY ROUTE TO [modelking] WITH GRANT OPTION
GRANT ALTER ANY SCHEMA TO [modelking] WITH GRANT OPTION
GRANT ALTER ANY SERVICE TO [modelking] WITH GRANT OPTION
GRANT ALTER ANY SYMMETRIC KEY TO [modelking] WITH GRANT OPTION
GRANT ALTER ANY USER TO [modelking] WITH GRANT OPTION
GRANT ALTER TO [modelking] WITH GRANT OPTION
GRANT AUTHENTICATE TO [modelking] WITH GRANT OPTION
GRANT BACKUP DATABASE TO [modelking] WITH GRANT OPTION
GRANT BACKUP LOG TO [modelking] WITH GRANT OPTION
GRANT CHECKPOINT TO [modelking] WITH GRANT OPTION
GRANT CONNECT REPLICATION TO [modelking] WITH GRANT OPTION
GRANT CONTROL TO [modelking] WITH GRANT OPTION
GRANT CREATE AGGREGATE TO [modelking] WITH GRANT OPTION
GRANT CREATE ASSEMBLY TO [modelking] WITH GRANT OPTION
GRANT CREATE ASYMMETRIC KEY TO [modelking] WITH GRANT OPTION
GRANT CREATE CERTIFICATE TO [modelking] WITH GRANT OPTION
GRANT CREATE CONTRACT TO [modelking] WITH GRANT OPTION
GRANT CREATE DATABASE DDL EVENT NOTIFICATION TO [modelking] WITH GRANT OPTION
GRANT CREATE DEFAULT TO [modelking] WITH GRANT OPTION
GRANT CREATE FULLTEXT CATALOG TO [modelking] WITH GRANT OPTION
GRANT CREATE FUNCTION TO [modelking] WITH GRANT OPTION
GRANT CREATE MESSAGE TYPE TO [modelking] WITH GRANT OPTION
GRANT CREATE PROCEDURE TO [modelking] WITH GRANT OPTION
GRANT CREATE QUEUE TO [modelking] WITH GRANT OPTION
GRANT CREATE REMOTE SERVICE BINDING TO [modelking] WITH GRANT OPTION
GRANT CREATE ROLE TO [modelking] WITH GRANT OPTION
GRANT CREATE ROUTE TO [modelking] WITH GRANT OPTION
GRANT CREATE RULE TO [modelking] WITH GRANT OPTION
GRANT CREATE SCHEMA TO [modelking] WITH GRANT OPTION
GRANT CREATE SERVICE TO [modelking] WITH GRANT OPTION
GRANT CREATE SYMMETRIC KEY TO [modelking] WITH GRANT OPTION
GRANT CREATE SYNONYM TO [modelking] WITH GRANT OPTION
GRANT CREATE TABLE TO [modelking] WITH GRANT OPTION
GRANT CREATE TYPE TO [modelking] WITH GRANT OPTION
GRANT CREATE VIEW TO [modelking] WITH GRANT OPTION
GRANT CREATE XML SCHEMA COLLECTION TO [modelking] WITH GRANT OPTION
GRANT DELETE TO [modelking] WITH GRANT OPTION
GRANT EXECUTE TO [modelking] WITH GRANT OPTION
GRANT INSERT TO [modelking] WITH GRANT OPTION
GRANT REFERENCES TO [modelking] WITH GRANT OPTION
GRANT SELECT TO [modelking] WITH GRANT OPTION
GRANT SHOWPLAN TO [modelking] WITH GRANT OPTION
GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [modelking] WITH GRANT OPTION
GRANT TAKE OWNERSHIP TO [modelking] WITH GRANT OPTION
GRANT UPDATE TO [modelking] WITH GRANT OPTION
GRANT VIEW DATABASE STATE TO [modelking] WITH GRANT OPTION
GRANT VIEW DEFINITION TO [modelking] WITH GRANT OPTION
GO

-- 0.2.4
-- schema permissions
--GRANT ALTER ANY APPLICATION ROLE TO [modelking] WITH GRANT OPTION 
--GRANT ALTER ANY ASSEMBLY TO [modelking] WITH GRANT OPTION 
--GRANT ALTER ANY ASYMMETRIC KEY TO [modelking] WITH GRANT OPTION 
--GRANT ALTER ANY CERTIFICATE TO [modelking] WITH GRANT OPTION 
--GRANT ALTER ANY CONTRACT TO [modelking] WITH GRANT OPTION 
--GRANT ALTER ANY DATABASE AUDIT TO [modelking] WITH GRANT OPTION 
--GRANT ALTER ANY DATABASE DDL TRIGGER TO [modelking] WITH GRANT OPTION 
--GRANT ALTER ANY DATABASE EVENT NOTIFICATION TO [modelking] WITH GRANT OPTION 
--GRANT ALTER ANY DATASPACE TO [modelking] WITH GRANT OPTION 
--GRANT ALTER ANY FULLTEXT CATALOG TO [modelking] WITH GRANT OPTION 
--GRANT ALTER ANY MESSAGE TYPE TO [modelking] WITH GRANT OPTION 
--GRANT ALTER ANY REMOTE SERVICE BINDING TO [modelking] WITH GRANT OPTION 
--GRANT ALTER ANY ROLE TO [modelking] WITH GRANT OPTION 
--GRANT ALTER ANY ROUTE TO [modelking] WITH GRANT OPTION 
--GRANT ALTER ANY SCHEMA TO [modelking] WITH GRANT OPTION 
--GRANT ALTER ANY SERVICE TO [modelking] WITH GRANT OPTION 
--GRANT ALTER ANY SYMMETRIC KEY TO [modelking] WITH GRANT OPTION 
--GRANT ALTER ANY USER TO [modelking] WITH GRANT OPTION 
--GRANT ALTER TO [modelking] WITH GRANT OPTION 
--GRANT AUTHENTICATE TO [modelking] WITH GRANT OPTION 
--GRANT BACKUP DATABASE TO [modelking] WITH GRANT OPTION 
--GRANT BACKUP LOG TO [modelking] WITH GRANT OPTION 
--GRANT CHECKPOINT TO [modelking] WITH GRANT OPTION 
--GRANT CONNECT REPLICATION TO [modelking] WITH GRANT OPTION 
--GRANT CONNECT TO [modelking] WITH GRANT OPTION 
--GRANT CONTROL TO [modelking] WITH GRANT OPTION 
--GRANT CREATE AGGREGATE TO [modelking] WITH GRANT OPTION 
--GRANT CREATE ASSEMBLY TO [modelking] WITH GRANT OPTION 
--GRANT CREATE ASYMMETRIC KEY TO [modelking] WITH GRANT OPTION 
--GRANT CREATE CERTIFICATE TO [modelking] WITH GRANT OPTION 
--GRANT CREATE CONTRACT TO [modelking] WITH GRANT OPTION 
--GRANT CREATE DATABASE DDL EVENT NOTIFICATION TO [modelking] WITH GRANT OPTION 
--GRANT CREATE DEFAULT TO [modelking] WITH GRANT OPTION 
--GRANT CREATE FULLTEXT CATALOG TO [modelking] WITH GRANT OPTION 
--GRANT CREATE FUNCTION TO [modelking] WITH GRANT OPTION 
--GRANT CREATE MESSAGE TYPE TO [modelking] WITH GRANT OPTION 
--GRANT CREATE PROCEDURE TO [modelking] WITH GRANT OPTION 
--GRANT CREATE QUEUE TO [modelking] WITH GRANT OPTION 
--GRANT CREATE REMOTE SERVICE BINDING TO [modelking] WITH GRANT OPTION 
--GRANT CREATE ROLE TO [modelking] WITH GRANT OPTION 
--GRANT CREATE ROUTE TO [modelking] WITH GRANT OPTION 
--GRANT CREATE RULE TO [modelking] WITH GRANT OPTION 
--GRANT CREATE SCHEMA TO [modelking] WITH GRANT OPTION 
--GRANT CREATE SERVICE TO [modelking] WITH GRANT OPTION 
--GRANT CREATE SYMMETRIC KEY TO [modelking] WITH GRANT OPTION 
--GRANT CREATE SYNONYM TO [modelking] WITH GRANT OPTION 
--GRANT CREATE TABLE TO [modelking] WITH GRANT OPTION 
--GRANT CREATE TYPE TO [modelking] WITH GRANT OPTION 
--GRANT CREATE VIEW TO [modelking] WITH GRANT OPTION 
--GRANT CREATE XML SCHEMA COLLECTION TO [modelking] WITH GRANT OPTION 
--GRANT DELETE TO [modelking] WITH GRANT OPTION 
--GRANT EXECUTE TO [modelking] WITH GRANT OPTION 
--GRANT INSERT TO [modelking] WITH GRANT OPTION 
--GRANT REFERENCES TO [modelking] WITH GRANT OPTION 
--GRANT SELECT TO [modelking] WITH GRANT OPTION 
--GRANT SHOWPLAN TO [modelking] WITH GRANT OPTION 
--GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [modelking] WITH GRANT OPTION 
--GRANT TAKE OWNERSHIP TO [modelking] WITH GRANT OPTION 
--GRANT UPDATE TO [modelking] WITH GRANT OPTION 
--GRANT VIEW DATABASE STATE TO [modelking] WITH GRANT OPTION 
--GRANT VIEW DEFINITION TO [modelking] WITH GRANT OPTION 
--GRANT ALTER ON SCHEMA::[Framework] TO [modelking] WITH GRANT OPTION 
--GRANT CONTROL ON SCHEMA::[Framework] TO [modelking] WITH GRANT OPTION 
--GRANT DELETE ON SCHEMA::[Framework] TO [modelking] WITH GRANT OPTION 
--GRANT EXECUTE ON SCHEMA::[Framework] TO [modelking] WITH GRANT OPTION 
--GRANT INSERT ON SCHEMA::[Framework] TO [modelking] WITH GRANT OPTION 
--GRANT REFERENCES ON SCHEMA::[Framework] TO [modelking] WITH GRANT OPTION 
--GRANT SELECT ON SCHEMA::[Framework] TO [modelking] WITH GRANT OPTION 
--GRANT TAKE OWNERSHIP ON SCHEMA::[Framework] TO [modelking] WITH GRANT OPTION 
--GRANT UPDATE ON SCHEMA::[Framework] TO [modelking] WITH GRANT OPTION 
--GRANT VIEW CHANGE TRACKING ON SCHEMA::[Framework] TO [modelking] WITH GRANT OPTION 
--GRANT VIEW DEFINITION ON SCHEMA::[Framework] TO [modelking] WITH GRANT OPTION 
--GO

--# TABLES

/****** Object:  Table [Framework].[Models]    Script Date: 05/24/2010 08:40:01 ******/
CREATE TABLE [Framework].[Objects_SWMM](
    OBJECTID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[ELEMENT_CLASS] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[TABLE_NAME] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[NAME_COLUMN] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [PRIMARY_KEY] varchar(20)  COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CLASS01] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CLASS02] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[CLASS03] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CLASS04] varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CLASS05] varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CLASS06] varchar(20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [Framework].[Models](
	[OBJECTID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[ModelName] [varchar](200) NOT NULL UNIQUE,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[CreatedOn] [datetime] DEFAULT GETDATE(),
	[Active] [bit] DEFAULT 1
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IdxModelsName ON [Framework].[Models](ModelName);
GO

CREATE TABLE Framework.Tags (
    OBJECTID int IDENTITY(1,1) PRIMARY KEY,
    Tag varchar(30) NOT NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[AssignedOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO

CREATE TABLE Framework.ModelTags (
    OBJECTID int IDENTITY(1,1) PRIMARY KEY,
    ModelID int FOREIGN KEY REFERENCES Framework.Models(OBJECTID),
    TagID int NOT NULL, 
    Active bit NOT NULL DEFAULT 1,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[AssignedOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY] 
GO

-- 0.2.4
CREATE TABLE Framework.SupportedSWMMBuilds (
    OBJECTID int IDENTITY(1,1) PRIMARY KEY,
    Build varchar(20) NOT NULL UNIQUE,
    DateAdded smalldatetime DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO Framework.SupportedSWMMBuilds (Build) VALUES ('5.0.021')
INSERT INTO Framework.SupportedSWMMBuilds (Build) VALUES ('5.0.022')
GO

-- 0.2.4
CREATE TABLE Framework.ModelVersions (
    OBJECTID int IDENTITY(1,1) PRIMARY KEY,
    ModelID int NOT NULL FOREIGN KEY REFERENCES Framework.Models(OBJECTID),
    TimeStamp datetime NOT NULL,
    Name varchar(200) NULL, -- 0.2.4 changed from "Version" to "Name"
    Description varchar(max) NULL,
    SWMMBuild varchar(20) NOT NULL FOREIGN KEY REFERENCES Framework.SupportedSWMMBuilds(Build), -- 0.2.4
	DocPath [varchar](max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, -- 0.2.4 changed doclink to DocPath and increased its size to max
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN() -- 0.2.4 removed AssignedOn column
) ON [PRIMARY]
GO

/****** Object:  Table [Framework].[ModelElements]    Script Date: 05/24/2010 16:19:02 ******/
CREATE TABLE [Framework].[ModelElements](
	[OBJECTID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[ModelID] int FOREIGN KEY REFERENCES Framework.Models(OBJECTID),
	[ElementClass] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[ElementID] [int] NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[TimeStamp] [datetime] NOT NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Active] [bit] DEFAULT 1
) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IdxModelElementsName ON [Framework].[ModelElements](ModelID);
GO

-- HYDROLOGY
-- Raingages
CREATE TABLE [Framework].[SWMM_RainGages](
	[OBJECTID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Name] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[RainType] [varchar](12) NOT NULL,
	[Interval] [varchar](5) NOT NULL,
	[SnowCatch] [float] NOT NULL,
	[Source] [varchar](12) NOT NULL,
	[SourceName] [varchar](500) NOT NULL,  -- Series name or file name
	[StationID] [varchar](20) NULL,
	[Units] varchar(2) NULL,
	[Description] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[XCoordinate] [float] NULL,
	[YCoordinate] [float] NULL,
	[Tag] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('RainGages', 'SWMM_RainGages', 'Name', 'OBJECTID', 'Hydrology', 'RainGages', NULL, NULL, NULL, NULL)
CREATE NONCLUSTERED INDEX IdxRaingageName ON [Framework].[SWMM_Raingages](Name);
GO

CREATE TABLE Framework.SWMM_Hydrographs (
    OBJECTID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name varchar(200) NOT NULL,
    UHGroup varchar(200) NOT NULL,
    Month varchar(3) NOT NULL,
    Response varchar(6) NOT NULL,
    RainGage varchar(60) NOT NULL,
    R float NOT NULL,
    T float NOT NULL,
    K float NOT NULL,
    IAmax float NOT NULL,
    IArec float NOT NULL,
    IAini float NOT NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('Hydrographs', 'SWMM_Hydrographs', 'Name', 'OBJECTID', 'Hydrology', 'Hydrographs', 'UHGroup', 'Month', 'Response', NULL)
CREATE NONCLUSTERED INDEX IdxHydrographsName ON [Framework].[SWMM_Hydrographs](Name);
GO

-- Subcatchments
CREATE TABLE [Framework].[SWMM_Subcatchments](
	[OBJECTID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Name] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Raingage] [varchar](200) NOT NULL,
	[Outlet] [varchar](200) NOT NULL,
	[Area] [float] NOT NULL,
	[PctImperv] [float] NOT NULL,
	[Width] [float] NOT NULL,
	[PctSlope] [float] NOT NULL,
    [CurbLength] [float] NOT NULL,
    [SnowPack] [varchar](24) NULL,
    [Description] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [SubareasDescription] varchar(7000) NULL,
    [GWDescription] varchar(7000) NULL,
    [InfiltrationDescription] varchar(7000),
	[NImperv] [float] NOT NULL,
	[NPerv] [float] NOT NULL,
	[SImperv] [float] NOT NULL,
	[SPerv] [float] NOT NULL,
	[PctZero] [float] NOT NULL,
	[RouteTo] [varchar](12) NOT NULL, 
	[PctRouted] [float] NULL,
	[SuctionHead] [float] NULL,
	[HydCon] [float] NULL,
	[IMDmax] [float] NULL,
    [Aquifer] varchar(50) NULL,
    [MaxRate] float NULL,
    [MinRate] float NULL,
    [Decay] float NULL,
    [DryTime] float NULL,
    [MaxInfil] float NULL,
    [GWReceivingNode] varchar(200) NULL,
    [GWSurfaceElev] float NULL,
    [GWFlowCoeff] float NULL,
    [GWFlowExpon] float NULL,
    [SWFlowCoeff] float NULL,
    [SWFlowExpon] float NULL,
    [SWGWInteractionCoeff] float NULL,
    [SWFixedDepth] float NULL,
    [GWThresholdElevation] float NULL,
    [Tag] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[GEOShapeID] [int] NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('Subcatchments', 'SWMM_Subcatchments', 'Name', 'OBJECTID', 'Hydrology', 'Subcatchments', NULL, NULL, NULL, NULL)
CREATE NONCLUSTERED INDEX IdxSubcatchmentName ON [Framework].[SWMM_Subcatchments](Name);
GO

CREATE TABLE Framework.SWMM_Coverages (
    OBJECTID int IDENTITY(1,1) NOT NULL,
    Name varchar(200) NOT NULL,
    Subcatchment varchar(200) NOT NULL,
    LandUse varchar(200) NOT NULL,
    PercentArea float NOT NULL,
    [Description] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('Coverages', 'SWMM_Coverages', 'Name', 'OBJECTID', 'Hydrology', 'Coverages', 'LandUse', NULL, NULL, NULL)
CREATE NONCLUSTERED INDEX IdxCoveragesName ON [Framework].[SWMM_Coverages](Name);
GO

-- Polygons
CREATE TABLE [Framework].[SWMM_PolygonPoints] (
    [OBJECTID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Name] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    Subcatchment varchar(200) NOT NULL,
    Ordinal int NOT NULL,
    [XCoordinate] [float] NOT NULL,
    [YCoordinate] [float] NOT NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('PolygonPoints', 'SWMM_PolygonPoints', 'Name', 'OBJECTID', 'Hydrology', 'PolygonPoints', 'Subcatchment', NULL, NULL, NULL)
CREATE NONCLUSTERED INDEX IdxPolygonSubcatchmentName ON [Framework].[SWMM_Polygons](Name);
GO

CREATE TABLE Framework.SWMM_Loadings (
    OBJECTID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name varchar(200) NOT NULL,
    Subcatchment varchar(200) NOT NULL,
    Pollutant varchar(200) NOT NULL,
    Loading float NOT NULL,
    [Description] varchar(7000) NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('Loadings', 'SWMM_Loadings', 'Name', 'OBJECTID', 'Hydrology', 'Loadings', 'Pollutant', NULL, NULL, NULL)
CREATE NONCLUSTERED INDEX IdxLoadingsName ON [Framework].[SWMM_Loadings](Name);
GO

-- Aquifers
CREATE TABLE [Framework].[SWMM_Aquifers](
	[OBJECTID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Name] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[Porosity] [float] NOT NULL,
	[WiltPoint] [float] NOT NULL,
	[FieldCapacity] [float] NOT NULL,
	[HydCon] [float] NOT NULL,
	[CondSlope] [float] NOT NULL,
	[TensionSlope] [float] NOT NULL,
	[UpperEvap] [float] NOT NULL,
	[LowerEvap] [float] NOT NULL,
	[LowerLoss] [float] NOT NULL,
	[BottomElev] [float] NOT NULL, 
	[WaterTable] [float] NOT NULL,
	[UpperMoist] [float] NOT NULL,
    [Description] varchar(7000) NULL,
    [GEOShapeID] [int] NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('Aquifers', 'SWMM_Aquifers','Name', 'OBJECTID', 'Hydrology', 'Aquifers', NULL, NULL, NULL, NULL)
CREATE NONCLUSTERED INDEX IdxAquiferName ON [Framework].[SWMM_Aquifers](Name);
GO

-- HYDRAULICS
-- Nodes
CREATE TABLE [Framework].[SWMM_Junctions](
	[OBJECTID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Name] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[InvertElevation] [float] NOT NULL,
	[MaxDepth] [float] NOT NULL,
	[InitDepth] [float] NOT NULL,
	[SurchargeDepth] [float] NOT NULL,
	[PondedArea] [float] NOT NULL,
	[Description] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[RDIIDescription] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [XCoordinate] [float] NULL,
    [YCoordinate] [float] NULL,
    [UnitHydrograph] varchar(200) NULL,
    [SewerArea] float NULL,
	[Tag] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[GEOShapeID] [int] NULL,
	[SmartMH] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('Junctions', 'SWMM_Junctions', 'Name', 'OBJECTID', 'Hydraulics', 'Nodes', 'Junctions', NULL, NULL, NULL)
CREATE NONCLUSTERED INDEX IdxJunctionName ON [Framework].[SWMM_Junctions](Name);
GO

CREATE TABLE [Framework].[SWMM_Outfalls](
	[OBJECTID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Name] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[InvertElevation] [float] NOT NULL,
	[OutfallType] [varchar](10) NOT NULL,
	[TimeSeriesName] [varchar](50) NULL,
	[TideGate] varchar(3) NOT NULL,
    [XCoordinate] [float] NULL,
    [YCoordinate] [float] NULL,
    [UnitHydrograph] varchar(200) NULL,
    [SewerArea] float NULL,
	[Description] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[RDIIDescription] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Tag] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[GEOShapeID] [int] NULL,
	[SmartMH] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('Outfalls', 'SWMM_Outfalls', 'Name', 'OBJECTID', 'Hydraulics', 'Nodes', 'Outfalls', NULL, NULL, NULL)
CREATE NONCLUSTERED INDEX IdxOutfallName ON [Framework].[SWMM_Outfalls](Name);
GO

CREATE TABLE [Framework].[SWMM_Storage](
	[OBJECTID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Name] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[InvertElevation] [float] NOT NULL,
	[MaxDepth] [float] NOT NULL,
	[InitDepth] [float] NOT NULL,
    [StorageCurve] [varchar](50) NOT NULL,
    [CurveName] [varchar](200) NULL,
    [CurveCoeff] [float] NULL,
    [CurveExponent] [float] NULL,
    [CurveConstant] [float] NULL,
	[PondedArea] [float] NOT NULL,
    [EvapFactor] [float] NOT NULL,
    [SuctionHead] [float] NULL,
    [Conductivity] [float] NULL,
    [InitialDeficit] [float] NULL,
	[Description] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[RDIIDescription] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [XCoordinate] [float] NULL,
    [YCoordinate] [float] NULL,
    [UnitHydrograph] varchar(200) NULL,
    [SewerArea] float NULL,
	[Tag] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[GEOShapeID] [int] NULL,
	[SmartMH] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('Storage', 'SWMM_Storage', 'Name', 'OBJECTID', 'Hydraulics', 'Nodes', 'Storage', NULL, NULL, NULL)
CREATE NONCLUSTERED INDEX IdxStorageName ON [Framework].[SWMM_Storage](Name);
GO

CREATE TABLE [Framework].[SWMM_NodeInflows] (
	[OBJECTID] [int] IDENTITY(1, 1) NOT NULL PRIMARY KEY,
	[Name] [varchar](200) NOT NULL,
    Node varchar(200) NOT NULL,
	[Parameter] [varchar](200) NOT NULL,
	[TimeSeries] [varchar](500) NULL,
    [ParameterType] [varchar](20) NULL,
	[UnitsFactor] [float] NULL,
	[ScaleFactor] [float] NULL,
	[BaselineValue] [float] NULL,
	[BaselinePattern] [varchar](20) NULL,
	[AvgValue] [float] NULL,
	[TimePatterns] [varchar](200) NULL,
    DWFDescription varchar(7000) NULL,
    InflowsDescription varchar(7000) NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('NodeInflows', 'SWMM_NodeInflows', 'Name', 'OBJECTID', 'Hydraulics', 'Nodes', 'NodeInflows', 'Parameter', NULL, NULL)
CREATE NONCLUSTERED INDEX IdxNodeInflowsName ON [Framework].[SWMM_NodeInflows](Name)
GO

CREATE TABLE Framework.SWMM_Controls (
    OBJECTID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name varchar(200) NOT NULL,
    RuleName varchar(200) NOT NULL,
    Ordinal int NOT NULL,
    RuleText varchar(500) NOT NULL,
    Description varchar(7000) NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('Controls', 'SWMM_Controls', 'Name', 'OBJECTID', 'Hydraulics', 'Controls', NULL, NULL, NULL, NULL)
CREATE NONCLUSTERED INDEX IdxControlsName ON [Framework].[SWMM_Controls](Name)
GO

CREATE TABLE Framework.SWMM_Treatments (
    OBJECTID int IDENTITY(1, 1) NOT NULL PRIMARY KEY,
    Name varchar(200) NOT NULL,
    Node varchar(200) NOT NULL,
    Pollutant varchar(200) NOT NULL,
    Formula varchar(100) NOT NULL,
    Description varchar(7000) NULL, 
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('Treatments', 'SWMM_Treatments', 'Name', 'OBJECTID', 'Hydraulics', 'Nodes', 'Treatments', 'Pollutant', NULL, NULL)
CREATE NONCLUSTERED INDEX IdxTreatmentsName ON [Framework].[SWMM_Treatments](Name)
GO

-- Links
CREATE TABLE [Framework].[SWMM_Conduits](
	[OBJECTID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Name] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[InletNode] [varchar](200) NOT NULL,
	[OutletNode] [varchar](200) NOT NULL,
	[Length] [float] NOT NULL,
	[ManningN] [float] NOT NULL,
	[InletOffset] [float] NOT NULL,
	[OutletOffset] [float] NOT NULL,
	[InitFlow] [float] NOT NULL,
	[MaxFlow] [float] NOT NULL,
	[Description] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[LossesDescription] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[XSectionsDescription] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[EntryLoss] [float] NOT NULL, 
	[ExitLoss] [float] NOT NULL,
	[AvgLoss] [float] NOT NULL,
	[FlapGate] [varchar](3) NOT NULL, 
	[Shape] [varchar](20) NOT NULL,
    [Geom1] [float] NOT NULL,
    [Geom2] [float] NOT NULL,
    [Geom3] [float] NOT NULL,
    [Geom4] [float] NOT NULL,
	[Barrels] [float] NULL,
	[CulvertCode] [varchar](20) NULL,
	[Tag] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[GEOShapeID] [int] NULL,
    [TransLink] [varchar](20) NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('Conduits', 'SWMM_Conduits', 'Name', 'OBJECTID', 'Hydraulics', 'Links', 'Conduits', NULL, NULL, NULL)
CREATE NONCLUSTERED INDEX IdxConduitName ON [Framework].[SWMM_Conduits](Name);
GO

CREATE TABLE [Framework].[SWMM_Profiles] (
    OBJECTID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name varchar(200) NOT NULL,
    Profile varchar(200) NOT NULL,
    Link varchar(200) NOT NULL,
    Ordinal int NOT NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('Profiles', 'SWMM_Profiles', 'Name', 'OBJECTID', 'Hydraulics', 'Links', 'Profiles', NULL, NULL, NULL)
CREATE NONCLUSTERED INDEX IdxConduitName ON [Framework].[SWMM_Profiles](Name);
GO

CREATE TABLE [Framework].[SWMM_Pumps](
	[OBJECTID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Name] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[InletNode] [varchar](200) NOT NULL,
	[OutletNode] [varchar](200) NOT NULL,
	[PumpCurve] [varchar](200) NOT NULL,
	[InitStatus] [varchar](3) NOT NULL,
	[StartupDepth] [float] NOT NULL,
	[ShutoffDepth] [float] NOT NULL,
    [Description] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [GEOShapeID] [int] NULL,
    [Tag] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('Pumps', 'SWMM_Pumps', 'Name', 'OBJECTID', 'Hydraulics', 'Links', 'Pumps', NULL, NULL, NULL)
CREATE NONCLUSTERED INDEX IdxPumpName ON [Framework].[SWMM_Pumps](Name);
GO

CREATE TABLE [Framework].[SWMM_Orifices](
	[OBJECTID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Name] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[InletNode] [varchar](200) NOT NULL,
	[OutletNode] [varchar](200) NOT NULL,
	[Type] [varchar](12) NOT NULL,
	[InletOffset] [float] NOT NULL,
	[DischargeCoeff] [float] NOT NULL,
	[FlapGate] [varchar](3) NOT NULL,
	[MoveTime] [float] NOT NULL,
	[Shape] [varchar](12) NULL,
	[Geom1] [float] NOT NULL,
	[Geom2] [float] NOT NULL,
    [Geom3] [float] NOT NULL,
    [Geom4] [float] NOT NULL,
    [Barrels] [float] NULL,
    [CulvertCode] varchar(20) NULL,
    [Description] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [XSectionsDescription] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Tag] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [GEOShapeID] [int] NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('Orifices', 'SWMM_Orifices', 'Name', 'OBJECTID', 'Hydraulics', 'Links', 'Orifices', NULL, NULL, NULL)
CREATE NONCLUSTERED INDEX IdxOrificeName ON [Framework].[SWMM_Orifices](Name);
GO

CREATE TABLE [Framework].[SWMM_Weirs](
	[OBJECTID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Name] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	[InletNode] [varchar](200) NOT NULL,
	[OutletNode] [varchar](200) NOT NULL,
	[Type] [varchar](12) NOT NULL,
	[InletOffset] [float] NOT NULL,
	[DischargeCoeff] [float] NOT NULL,
	[FlapGate] [varchar](3) NOT NULL,
	[EndContractions] [float] NOT NULL,
	[EndCoeff] [float] NOT NULL,
    [Description] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [XSectionsDescription] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Shape] [varchar](20) NOT NULL,
	[Geom1] [float] NOT NULL,
	[Geom2] [float] NOT NULL,
	[Geom3] [float] NOT NULL,
    [Geom4] [float] NOT NULL,
    [Barrels] [float] NULL,
    [CulvertCode] varchar(20) NULL,
    [Tag] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [GEOShapeID] [int] NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('Weirs', 'SWMM_Weirs','Name', 'OBJECTID', 'Hydraulics', 'Links', 'Weirs', NULL, NULL, NULL)
CREATE NONCLUSTERED INDEX IdxWeirName ON [Framework].[SWMM_Weirs](Name);
GO

-- 0.2.3
CREATE TABLE Framework.SWMM_Outlets (
    OBJECTID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	Name varchar(200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
	InletNode varchar(200) NOT NULL,
	OutletNode varchar(200) NOT NULL,
    OutflowHeight float NOT NULL,
    OutletType varchar(100) NOT NULL,
    FunctionalCoeff float NULL,
    FunctionalExponent float NULL,
    CurveName varchar(200) NULL,
    FlapGate varchar(3) NOT NULL,
    [Description] [varchar](7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Tag] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('Outlets', 'SWMM_Outlets', 'Name', 'OBJECTID', 'Hydraulics', 'Links', 'Outlets', NULL, NULL, NULL)
CREATE NONCLUSTERED INDEX IdxOutletName ON [Framework].[SWMM_Outlets](Name);
GO

CREATE TABLE [Framework].[SWMM_Vertices](
    [OBJECTID] int IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[Name] [varchar](200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    Link varchar(200) NOT NULL,
    [XCoordinate] [float] NOT NULL,
    [YCoordinate] [float] NOT NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('Vertices', 'SWMM_Vertices', 'Name', 'OBJECTID', 'Hydraulics', 'Link', 'Vertices', NULL, NULL, NULL)
CREATE NONCLUSTERED INDEX IdxVerticesName ON [Framework].[SWMM_Vertices](Name);
GO

CREATE TABLE Framework.SWMM_TransectPoints (
    OBJECTID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name varchar(200) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    TransectName varchar(200) NOT NULL,
    Ordinal int NOT NULL,
    StationCount int NOT NULL,
    LeftBankRoughness float NOT NULL,
    RightBankRoughness float NOT NULL,
    ChannelRoughness float NOT NULL,
    LeftBankStation float NOT NULL,
    RightBankStation float NOT NULL,
    StationsModifier float NOT NULL,
    ElevationsModifier float NOT NULL,
    MeanderModifier float NOT NULL,
    Description varchar(7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    Station_ft float NOT NULL,
    Elevation_ft float NOT NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('TransectPoints', 'SWMM_TransectPoints', 'Name', 'OBJECTID', 'Hydraulics', NULL, NULL, NULL, NULL, NULL)
CREATE NONCLUSTERED INDEX IdxTransectPointsName ON [Framework].[SWMM_TransectPoints](Name);
GO

-- Quality
-- Pollutants
CREATE TABLE Framework.SWMM_Pollutants (
    OBJECTID INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name varchar(200) NOT NULL,
    MassUnits varchar(6) NOT NULL,
    RainConcen float NOT NULL,
    GWConcen float NOT NULL,
    IIConcen float NOT NULL,
    DecayCoeff float NOT NULL,
    SnowOnly varchar(3) NOT NULL,
    CoPollutant varchar(200) NOT NULL,
    CoPollutantFraction float NOT NULL,
    DWFConcen float NOT NULL,
    Description varchar(7000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('Pollutants', 'SWMM_Pollutants', 'Name', 'OBJECTID', 'Quality', 'Pollutants', NULL, NULL, NULL, NULL)
CREATE NONCLUSTERED INDEX IdxPollutantsName ON [Framework].[SWMM_Pollutants](Name);
GO

CREATE TABLE Framework.SWMM_LandUses (
    OBJECTID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name varchar(200) NOT NULL,
    CleaningInterval float NOT NULL,
    Availability float NOT NULL,
    LastCleaned float NOT NULL,
    Description varchar(7000) NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('LandUses', 'SWMM_LandUses', 'Name', 'OBJECTID', 'Quality', 'LandUses', NULL, NULL, NULL, NULL)
CREATE NONCLUSTERED INDEX IdxLandUsesName ON [Framework].[SWMM_LandUses](Name);
GO

CREATE TABLE Framework.SWMM_BuildUp (
    OBJECTID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name varchar(200) NOT NULL,
    LandUse varchar(200) NOT NULL,
    Pollutant varchar(200) NOT NULL,
    Formula varchar(4) NOT NULL,
    Coeff1 float NOT NULL,
    Coeff2 float NOT NULL,
    Coeff3 float NULL,
    TimeSeries varchar(200) NULL,
    Normalizer varchar(4) NOT NULL,
    Description varchar(7000) NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('BuildUp', 'SWMM_BuildUp', 'Name', 'OBJECTID', 'Quality', 'BuildUp', 'Pollutant', NULL, NULL, NULL)
CREATE NONCLUSTERED INDEX IdxBuildUpName ON [Framework].[SWMM_BuildUp](Name);
GO

CREATE TABLE Framework.SWMM_WashOff (
    OBJECTID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name varchar(200) NOT NULL,
    LandUse varchar(200) NOT NULL,
    Pollutant varchar(200) NOT NULL,
    Formula varchar(4) NOT NULL,
    Coeff1 float NOT NULL,
    Coeff2 float NOT NULL,
    CleaningEfficiency float NOT NULL,
    BMPEfficiency float NOT NULL,
    Description varchar(7000) NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('WashOff', 'SWMM_WashOff', 'Name', 'OBJECTID', 'Quality', 'WashOff', 'Pollutant', NULL, NULL, NULL)
CREATE NONCLUSTERED INDEX IdxBuildUpName ON [Framework].[SWMM_WashOff](Name);
GO

CREATE TABLE Framework.SWMM_TimeSeriesPoints (
    OBJECTID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Name varchar(200) NOT NULL,
    TimeSeries varchar(200) NOT NULL,
    Ordinal int NOT NULL,
    FileName varchar(7000) NULL,
    DateTime datetime NULL,
    Duration float NULL,
    Value float NULL,
    Description varchar(7000) NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('TimeSeriesPoints', 'SWMM_TimeSeriesPoints', 'Name', 'OBJECTID', 'TimeSeriesPoints', 'TimeSeries', NULL, NULL, NULL, NULL)
CREATE NONCLUSTERED INDEX IdxTSPointsName ON [Framework].[SWMM_TimeSeriesPoints](Name);
GO

-- TITLE/NOTES
CREATE TABLE Framework.SWMM_Notes (
    OBJECTID int IDENTITY(1, 1) PRIMARY KEY,
    NotesText varchar(8000) NOT NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('Notes', 'SWMM_Notes', NULL, 'OBJECTID', 'Notes', NULL, NULL, NULL, NULL, NULL)
GO

-- OPTIONS
CREATE TABLE Framework.SWMM_Options (
    OBJECTID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    FLOW_UNITS varchar(5) NULL,
    INFILTRATION varchar(20) NULL,
    FLOW_ROUTING varchar(20) NULL,
    START_DATE varchar(20) NULL,
    START_TIME varchar(20) NULL,
    REPORT_START_DATE varchar(20) NULL,
    REPORT_START_TIME varchar(20) NULL,
    END_DATE varchar(20) NULL,
    END_TIME varchar(20) NULL,
    SWEEP_START varchar(20) NULL,
    SWEEP_END varchar(20) NULL,
    DRY_DAYS float NULL,
    REPORT_STEP varchar(20) NULL,
    WET_STEP varchar(20) NULL,
    DRY_STEP varchar(20) NULL,
    ROUTING_STEP varchar(20) NULL,
    ALLOW_PONDING varchar(3) NULL,
    INERTIAL_DAMPING varchar(20) NULL,
    VARIABLE_STEP float NULL,
    LENGTHENING_STEP float NULL,
    MIN_SURFAREA float NULL,
    NORMAL_FLOW_LIMITED varchar(20) NULL,
    SKIP_STEADY_STATE varchar(3) NULL,
    FORCE_MAIN_EQUATION varchar(20) NULL,
    LINK_OFFSETS varchar(20) NULL,
    MIN_SLOPE varchar(20) NULL,
    IGNORE_RAINFALL varchar(3) NULL,
    IGNORE_GROUNDWATER varchar(3) NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('Options', 'SWMM_Options', NULL, 'OBJECTID', 'Options', NULL, NULL, NULL, NULL, NULL)
GO

CREATE TABLE Framework.SWMM_Report (
    OBJECTID int IDENTITY(1,1),
    INPUT varchar(3) NOT NULL,
    CONTROLS varchar(3) NOT NULL,
    SUBCATCHMENTS varchar(500) NOT NULL,
    NODES varchar(500) NOT NULL,
    LINKS varchar(500) NOT NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('Report', 'SWMM_Report', NULL, 'OBJECTID', 'Report', NULL, NULL, NULL, NULL, NULL)
GO

CREATE TABLE Framework.SWMM_Maps (
    OBJECTID int IDENTITY(1,1),
    LLXCoordinate float NOT NULL,
    LLYCoordinate float NOT NULL,
    URXCoordinate float NOT NULL,
    URYCoordinate float NOT NULL,
    Units varchar(10) NOT NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('Maps', 'SWMM_Maps', NULL, 'OBJECTID', 'Maps', NULL, NULL, NULL, NULL, NULL)
GO

CREATE TABLE Framework.SWMM_Files (
    OBJECTID int IDENTITY(1,1) NOT NULL,
    Name varchar(200) NOT NULL,
    Usage varchar(4) NOT NULL,
    FileType varchar(8) NOT NULL,
    FileName varchar(7000) NOT NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('Files', 'SWMM_Files', 'Name', 'OBJECTID', 'Files', NULL, NULL, NULL, NULL, NULL)
CREATE NONCLUSTERED INDEX IdxFilesName ON [Framework].[SWMM_Files](Name);
GO

CREATE TABLE Framework.SWMM_Evaporation (
    OBJECTID int IDENTITY(1,1),
    Type varchar(10) NOT NULL, 
    Parameters varchar(500) NOT NULL,
    Recovery varchar(50) NULL,
    DryOnly varchar(3) NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('Evaporation', 'SWMM_Evaporation', NULL, 'OBJECTID', 'Evaporation', NULL, NULL, NULL, NULL, NULL)
GO

CREATE TABLE Framework.SWMM_PatternMultipliers (
    OBJECTID int IDENTITY(1,1),
    Name varchar(200) NOT NULL,
    Pattern varchar(200) NOT NULL,
    Type varchar(7) NOT NULL,
    Ordinal int NOT NULL,
    Multiplier varchar(500) NOT NULL,
    Description varchar(7000) NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('PatternMultipliers', 'SWMM_PatternMultipliers', 'Name', 'OBJECTID', 'PatternMultipliers', 'Type', 'Pattern', NULL, NULL, NULL)
CREATE NONCLUSTERED INDEX IdxPatMultName ON [Framework].[SWMM_PatternMultipliers](Name);
GO

CREATE TABLE Framework.SWMM_CurvePoints (
    OBJECTID int IDENTITY(1,1) NOT NULL,
    Name varchar(200) NOT NULL,
    Curve varchar(200) NOT NULL,
    Type varchar(15) NOT NULL,
    Ordinal int NOT NULL,
    XCoordinate float NOT NULL,
    YCoordinate float NOT NULL,
    Description varchar(7000) NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO
INSERT INTO [Framework].[Objects_SWMM] (ELEMENT_CLASS, TABLE_NAME, NAME_COLUMN, PRIMARY_KEY,
    CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06)
    Values ('CurvePoints', 'SWMM_CurvePoints', 'Name', 'OBJECTID', 'CurvePoints', 'Type', 'Curve', NULL, NULL, NULL)
CREATE NONCLUSTERED INDEX IdxCurvePointsName ON [Framework].[SWMM_CurvePointsName](Name);
GO

CREATE TABLE Framework.SWMM_INPBackup (
    OBJECTID int IDENTITY(1,1),
    ModelID int NOT NULL,
    INP NTEXT NOT NULL,
	[Comment] [varchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
	[User] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS DEFAULT ORIGINAL_LOGIN(),
	[createdOn] [datetime] DEFAULT GETDATE()
) ON [PRIMARY]
GO

--# PROCS

IF OBJECT_ID('Framework.spSetSchemaVersion') IS NOT NULL
    DROP PROCEDURE Framework.spSetSchemaVersion
GO
CREATE PROCEDURE Framework.spSetSchemaVersion 
@Version varchar(16),
@Notes varchar(max) = NULL
WITH EXECUTE AS OWNER
AS
BEGIN
    INSERT INTO Framework.SchemaVersion (VERSION, NOTES) VALUES (@Version, @Notes)
END
GO

IF OBJECT_ID('Framework.ufnGetTempFolder') IS NOT NULL
    DROP FUNCTION Framework.ufnGetTempFolder;
GO
CREATE FUNCTION Framework.ufnGetTempFolder()
RETURNS nvarchar(max)
WITH EXECUTE AS OWNER
AS 
BEGIN
    DECLARE @client varchar(60)
    DECLARE @server varchar(60)
    SET @client = HOST_NAME()
    SET @server = CAST(SERVERPROPERTY('MACHINENAME') AS varchar(60))

    DECLARE @temp_dir varchar(max)
    IF @client = @server  
        SET @temp_dir = 'C:\SWMMR'
    ELSE
        SET @temp_dir = '\\' + CAST(SERVERPROPERTY('MACHINENAME') AS varchar(50))  + '\SWMMR'

    RETURN @temp_dir
END
GO

IF OBJECT_ID('Framework.ufnGetSchemaVersion') IS NOT NULL
    DROP FUNCTION Framework.ufnGetSchemaVersion;
GO
CREATE FUNCTION Framework.ufnGetSchemaVersion()
RETURNS varchar(10)
WITH EXECUTE AS OWNER
AS 
BEGIN
    DECLARE @max_sysdate datetime
    SELECT @max_sysdate = MAX(TIMESTAMP)
    FROM Framework.SchemaVersion

    DECLARE @version varchar(20)
    SELECT @version = VERSION
    FROM Framework.SchemaVersion
    WHERE TIMESTAMP = @max_sysdate

    RETURN @version
END
GO

IF OBJECT_ID('Framework.ufnGetDiffTableDef') IS NOT NULL
    DROP FUNCTION Framework.ufnGetDiffTableDef;
GO
CREATE FUNCTION Framework.ufnGetDiffTableDef()
RETURNS varchar(max)
WITH EXECUTE AS OWNER
AS
BEGIN
    DECLARE @transaction varchar(max)
    SET @transaction = '
        CREATE TABLE #ModelDiff (
            OBJECTID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
            Model1_ElementID int NULL,
            Model2_ElementID int NULL,
            [ElementClass] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
            Class01 [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
            Class02 [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
            Class03 [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
            Class04 [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
            Class05 [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
            Class06 [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
        );
        CREATE INDEX IX_ModelDiff_included ON #ModelDiff(OBJECTID) INCLUDE (Class01, Class02, Class03, Class04, Class05, Class06);
    '
    RETURN @transaction
END
GO

IF OBJECT_ID('Framework.ufnModelDiffValuesTableDef') IS NOT NULL
    DROP FUNCTION Framework.ufnGetModelDiffValuesTableDef;
GO
CREATE FUNCTION Framework.ufnGetDiffDifferencesTableDef()
RETURNS varchar(max)
WITH EXECUTE AS OWNER
AS
BEGIN
    DECLARE @transaction varchar(max)
    SET @transaction = '
        CREATE TABLE #ModelDiffValues (
            OBJECTID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
            ModelDiffID int NOT NULL,
            ParameterName varchar(50) NOT NULL,
            Model1_Value varchar(700) NOT NULL,
            Model2_Value varchar(700) NOT NULL
        );
        CREATE INDEX IX_ModelDiffDifferences_included ON #Cascading_ModelElements(OBJECTID) INCLUDE (Class01, Class02, Class03, Class04, Class05, Class06);
    '
    RETURN @transaction
END
GO

IF OBJECT_ID('Framework.ufnGetCMETableDef') IS NOT NULL
    DROP FUNCTION Framework.ufnGetCMETableDef;
GO
CREATE FUNCTION Framework.ufnGetCMETableDef()
RETURNS varchar(max)
WITH EXECUTE AS OWNER
AS
BEGIN
    DECLARE @transaction varchar(max)
    SET @transaction = '
        CREATE TABLE #Cascading_ModelElements ( 
            [OBJECTID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY, 
            [ModelID] int NOT NULL,
            [ModelName] varchar(200) NOT NULL,
            [ElementName] [varchar](200), 
            [New_ElementID] [int] NULL, 
            [Old_ElementID] [int] NULL, 
            [ElementClass] [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
            Class01 [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
            Class02 [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
            Class03 [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
            Class04 [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
            Class05 [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
            Class06 [varchar](40) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, 
            [FieldDifferences] [varchar](7000) NULL, 
            [Active] [bit] NULL DEFAULT ((1)), 
            [CommitValue] [bit] DEFAULT ((0)) 
        );
        CREATE INDEX IX_CME_included ON #Cascading_ModelElements(OBJECTID) INCLUDE (Class01, Class02, Class03, Class04, Class05, Class06);'
    RETURN @transaction
END
GO

IF OBJECT_ID('Framework.ufnGetFCTableDef') IS NOT NULL
    DROP FUNCTION Framework.ufnGetFCTableDef;
GO
CREATE FUNCTION Framework.ufnGetFCTableDef()
RETURNS varchar(max)
WITH EXECUTE AS OWNER
AS
BEGIN
    DECLARE @transaction varchar(max)
    SET @transaction = '
    CREATE TABLE #FieldChanges ( 
        OBJECTID int IDENTITY(1,1) NOT NULL PRIMARY KEY, 
        CME_ID int NOT NULL, 
        NewElementID int NOT NULL, 
        CurrentElementID int NOT NULL, 
        NewValue varchar(7000) NULL, 
        CurrentValue varchar(7000) NULL, 
        TableName varchar(20) NOT NULL, 
        FieldName varchar(20) NOT NULL, 
        FieldOrdinal int NOT NULL 
    );'

    RETURN @transaction
END
GO

IF OBJECT_ID('Framework.ufnSplitCSV') IS NOT NULL
    DROP FUNCTION Framework.ufnSplitCSV;
GO
CREATE FUNCTION Framework.ufnSplitCSV(
@CSVString varchar(max),
@Delimiter char(1) = ',')
RETURNS @outtable TABLE (Value varchar(max))
AS
BEGIN
    DECLARE @pos int
    DECLARE @slice varchar(max)

    SELECT @pos = 1
    IF LEN(@CSVString) < 1 OR @CSVString IS NULL RETURN;

    WHILE @pos != 0 BEGIN
        SET @pos = CHARINDEX(@Delimiter, @CSVString)
        IF @pos != 0 
            SET @slice = LEFT(@CSVString, @pos - 1)
        ELSE
            SET @slice = @CSVString

        IF LEN(@slice) > 0
            INSERT INTO @outtable (Value) VALUES (@slice)

        SET @CSVString = RIGHT(@CSVString, LEN(@CSVString) - @pos)

        IF LEN(@CSVString) = 0
            BREAK
    END
    RETURN 
END
GO

IF OBJECT_ID('Framework.ufnGetModelID') IS NOT NULL
    DROP FUNCTION Framework.ufnGetModelID;
GO
CREATE FUNCTION Framework.ufnGetModelID (@ModelName varchar(200))
RETURNS int
WITH EXECUTE AS OWNER
AS
BEGIN
    DECLARE @model_id int
    SELECT @model_id = OBJECTID 
    FROM Framework.Models
    WHERE ModelName = @ModelName

    RETURN @model_id
END
GO

IF OBJECT_ID('Framework.ufnGetModelVersionID') IS NOT NULL
    DROP FUNCTION Framework.ufnGetModelVersionID;
GO
CREATE FUNCTION Framework.ufnGetModelVersionID (@ModelID int, @TimeUntil datetime = NULL)
RETURNS int
WITH EXECUTE AS OWNER
AS
BEGIN
    DECLARE @time_stamp datetime
    IF @TimeUntil IS NULL 
        SELECT @time_stamp = MAX(TimeStamp) FROM Framework.ModelVersions WHERE ModelID = @ModelID
    ELSE
        SELECT @time_stamp = MAX(TimeStamp) FROM Framework.ModelVersion WHERE ModelID = @ModelID AND TimeStamp <= @TimeUntil

    DECLARE @version_id int
    SELECT @version_id = OBJECTID 
    FROM Framework.ModelVersions
    WHERE ModelID = @ModelID
    AND TimeStamp = @time_stamp

    RETURN @version_id
END
GO

IF OBJECT_ID('Framework.spGetTagID') IS NOT NULL
    DROP PROCEDURE Framework.spGetTagID;
GO
CREATE PROCEDURE Framework.spGetTagID
@Tag varchar(30),
@TagID int OUTPUT
WITH EXECUTE AS OWNER
AS
BEGIN
    SELECT @TagID = OBJECTID 
    FROM Framework.Tags
    WHERE Tag = @Tag
END
GO

IF OBJECT_ID('Framework.spAddModelTags') IS NOT NULL
    DROP PROCEDURE Framework.spAddModelTags;
GO
CREATE PROCEDURE [Framework].[spAddModelTags]
@ModelID int,
@TagsCSV varchar(max)
WITH EXECUTE AS OWNER
AS
BEGIN
    SET XACT_ABORT ON
    SET NOCOUNT ON

    DECLARE @tags TABLE (Tag varchar(200))
    INSERT INTO @tags (Tag) 
        SELECT Value FROM Framework.ufnSplitCSV(@TagsCSV, ',')
              
    DECLARE @tag varchar(200)

    DECLARE tag_cursor CURSOR FOR
        SELECT Tag FROM @tags
        ORDER BY Tag

    OPEN tag_cursor 
    FETCH NEXT FROM tag_cursor 
    INTO @tag;

    WHILE @@FETCH_STATUS = 0 BEGIN
        DECLARE @tag_id int
        SET @tag_id = NULL

        EXEC Framework.spGetTagID @Tag = @tag, @TagID = @tag_id OUTPUT

        IF @tag_id IS NULL BEGIN
            INSERT INTO Framework.Tags (Tag) VALUES (@tag)
            SELECT @tag_id = IDENT_CURRENT('Framework.Tags')
        END

        DECLARE @modeltags_id int
        SET @modeltags_id = NULL
        DECLARE @active int
        SET @active = NULL

        SELECT @modeltags_id = OBJECTID, @active = Active
        FROM Framework.ModelTags
        WHERE ModelID = @ModelID AND TagID = @tag_id

        IF @modeltags_id IS NULL OR @active = 0
            INSERT INTO Framework.ModelTags (ModelID, TagID, Active)
            VALUES (@ModelID, @tag_id, 1)

        FETCH NEXT FROM tag_cursor 
        INTO @tag;
    END

    CLOSE tag_cursor;
    DEALLOCATE tag_cursor;
END
GO

IF OBJECT_ID('Framework.spSetModelTags') IS NOT NULL
    DROP PROCEDURE Framework.spSetModelTags;
GO
CREATE PROCEDURE [Framework].[spSetModelTags]
@TagsCSV varchar(max),
@ModelName varchar(200) = NULL,
@ModelID int = NULL
WITH EXECUTE AS OWNER
AS
BEGIN
    SET XACT_ABORT ON
    SET NOCOUNT ON

    IF @ModelID IS NULL 
        IF @ModelName IS NOT NULL
            SELECT @ModelID = Framework.ufnGetModelID(@ModelName)
        ELSE 
            RAISERROR(N'spSetModelTags needs @ModelName or @ModelID', 10, 1)

    DECLARE @tags TABLE (Tag varchar(200))
    INSERT INTO @tags (Tag) 
        SELECT Value FROM Framework.ufnSplitCSV(@TagsCSV, ',')
              
    DECLARE @tag varchar(200)

    DECLARE tag_cursor CURSOR FOR
        SELECT Tag FROM @tags
        ORDER BY Tag

    OPEN tag_cursor 
    FETCH NEXT FROM tag_cursor 
    INTO @tag;

    WHILE @@FETCH_STATUS = 0 BEGIN
        DECLARE @tag_id int
        SET @tag_id = NULL

        EXEC Framework.spGetTagID @Tag = @tag, @TagID = @tag_id OUTPUT

        IF @tag_id IS NULL BEGIN
            INSERT INTO Framework.Tags (Tag) VALUES (@tag)
            SELECT @tag_id = IDENT_CURRENT('Framework.Tags')
        END

        DECLARE @modeltags_id int
        SET @modeltags_id = NULL
        DECLARE @active int
        SET @active = NULL

        SELECT @modeltags_id = OBJECTID, @active = Active
        FROM Framework.ModelTags
        WHERE ModelID = @ModelID AND TagID = @tag_id

        IF @modeltags_id IS NULL OR @active = 0
            INSERT INTO Framework.ModelTags (ModelID, TagID, Active)
            VALUES (@ModelID, @tag_id, 1)

        FETCH NEXT FROM tag_cursor 
        INTO @tag;
    END

    CLOSE tag_cursor;
    DEALLOCATE tag_cursor;

    DECLARE @active_tags TABLE (Tag varchar(30))
    INSERT INTO @active_tags (Tag)
        EXEC Framework.spGetModelTags @ModelName = @ModelName

    INSERT INTO Framework.ModelTags (ModelID, TagID, Active)
        SELECT @ModelID, TagIDs.OBJECTID, 0
        FROM  (@active_tags AS AT LEFT OUTER JOIN @tags AS T 
              ON AT.Tag = T.Tag)  LEFT OUTER JOIN Framework.Tags AS TagIDs
              ON AT.Tag = TagIDs.Tag
        WHERE T.Tag IS NULL

END
GO

IF OBJECT_ID('Framework.ufnGetModelTagsCSV') IS NOT NULL
    DROP FUNCTION Framework.ufnGetModelTagsCSV;
GO
CREATE FUNCTION Framework.ufnGetModelTagsCSV (@ModelName varchar(200))
RETURNS varchar
WITH EXECUTE AS OWNER
AS
BEGIN
    DECLARE @model_id int
    SELECT @model_id = Framework.ufnGetModelID(@ModelName)

    DECLARE @active_tags TABLE (Tag varchar(30));

    WITH MaxModelTags AS (
        SELECT ModelID, TagID, Max(AssignedOn) AS MaxAssignedOn
        FROM Framework.ModelTags
        WHERE ModelID = @model_id 
        GROUP BY ModelID, TagID
    ),
    ModelTags AS (
        SELECT * 
        FROM Framework.ModelTags
        WHERE ModelID = @model_id
    )
    INSERT INTO @active_tags
    SELECT T.Tag 
    FROM (ModelTags AS MT INNER JOIN MaxModelTags as MMT
         ON MT.AssignedOn = MMT.MaxAssignedOn AND MT.TagID = MMT.TagID)
         INNER JOIN Framework.Tags AS T
            ON T.OBJECTID = MT.TagID
         WHERE MT.Active = 1 

    DECLARE @csv_str varchar(max)
    SELECT @csv_str = COALESCE(@csv_str + ', ', '') + Tag 
    FROM @active_tags
    ORDER BY Tag
    
    RETURN @csv_str
END
GO


IF OBJECT_ID('Framework.spGetModelTags') IS NOT NULL
    DROP PROCEDURE Framework.spGetModelTags;
GO
CREATE PROCEDURE Framework.spGetModelTags
@ModelName varchar(200)
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @model_id int
    SELECT @model_id = Framework.ufnGetModelID(@ModelName)

    DECLARE @active_tags TABLE (Tag varchar(30));

    WITH MaxModelTags AS (
        SELECT ModelID, TagID, Max(AssignedOn) AS MaxAssignedOn
        FROM Framework.ModelTags
        WHERE ModelID = @model_id 
        GROUP BY ModelID, TagID
    ),
    ModelTags AS (
        SELECT * 
        FROM Framework.ModelTags
        WHERE ModelID = @model_id
    )
    INSERT INTO @active_tags
    SELECT T.Tag 
    FROM (ModelTags AS MT INNER JOIN MaxModelTags as MMT
         ON MT.AssignedOn = MMT.MaxAssignedOn AND MT.TagID = MMT.TagID)
         INNER JOIN Framework.Tags AS T
            ON T.OBJECTID = MT.TagID
         WHERE MT.Active = 1 

    SELECT Tag 
    FROM @active_tags
    ORDER BY Tag
END
GO

IF OBJECT_ID('Framework.spGetTags') IS NOT NULL
    DROP PROCEDURE Framework.spGetTags;
GO
CREATE PROCEDURE Framework.spGetTags
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON;
    WITH MaxModelTags AS (
        SELECT ModelID, TagID, MAX(AssignedOn) as MaxAssignedOn
        FROM Framework.ModelTags
        GROUP BY ModelID, TagID
    )
    SELECT DISTINCT T.Tag 
    FROM (MaxModelTags AS MMT INNER JOIN Framework.ModelTags AS AMT
         ON MMT.ModelID = AMT.ModelID 
            AND MMT.TagID = AMT.TagID 
            AND MMT.MaxAssignedOn = AMT.AssignedOn) 
         INNER JOIN Framework.Tags AS T
         ON MMT.TagID = T.OBJECTID 
    WHERE AMT.Active = 1
    ORDER BY Tag
END
GO

-- 0.2.4
IF OBJECT_ID('Framework.spGetSupportedSWMMBuilds') IS NOT NULL
    DROP PROCEDURE Framework.spGetSupportedSWMMBuilds;
GO
CREATE PROCEDURE [Framework].[spGetSupportedSWMMBuilds]
WITH EXECUTE AS OWNER
AS
BEGIN
    SELECT Build FROM Framework.SupportedSWMMBuilds ORDER BY OBJECTID DESC
END
GO

-- 0.2.4 (updated)
IF OBJECT_ID('Framework.spGetModelVersions') IS NOT NULL
    DROP PROCEDURE Framework.spGetModelVersions;
GO
CREATE PROCEDURE [Framework].[spGetModelVersions]
@ModelName varchar(200)
WITH EXECUTE AS OWNER
AS
BEGIN
    DECLARE @model_id int
    SELECT @model_id = Framework.ufnGetModelID(@ModelName)

    SELECT OBJECTID, TimeStamp, [User], Name, SWMMBuild, DocPath, Description
    FROM Framework.ModelVersions
    WHERE ModelID = @model_id
    ORDER BY TimeStamp DESC
END
GO

IF OBJECT_ID('Framework.spGetModelVersion') IS NOT NULL
    DROP PROCEDURE Framework.spGetModelVersion;
GO
CREATE PROCEDURE [Framework].[spGetModelVersion]
@ModelName varchar(200),
@VersionID int = NULL,
@TimeUntil datetime = NULL
WITH EXECUTE AS OWNER
AS
BEGIN
    DECLARE @model_id int
    SELECT @model_id = Framework.ufnGetModelID(@ModelName)

    IF @VersionID IS NOT NULL
        SELECT @TimeUntil = TimeStamp
        FROM Framework.ModelVersions 
        WHERE ModelID = @model_id AND OBJECTID = @VersionID
    ELSE IF @TimeUntil IS NULL
        SET @TimeUntil = GETDATE()

    SELECT TOP(1) OBJECTID, TimeStamp, [User], Name, SWMMBuild, DocPath, Description
    FROM Framework.ModelVersions
    WHERE ModelID = @model_id AND TimeStamp <= @TimeUntil
    ORDER BY TimeStamp DESC
END
GO

-- updated 0.2.4
IF OBJECT_ID('Framework.spUpdateModelVersion') IS NOT NULL
    DROP PROCEDURE Framework.spUpdateModelVersion;
GO
CREATE PROCEDURE [Framework].[spUpdateModelVersion]
@VersionID int,
@Name varchar(200),
@SWMMBuild varchar(30),
@DocPath varchar(max),
@Description varchar(max)
WITH EXECUTE AS OWNER
AS
BEGIN
    SET XACT_ABORT ON
    UPDATE Framework.ModelVersions
    SET Name = @Name, SWMMBuild = @SWMMBuild, DocPath = @DocPath, Description = @Description
    WHERE OBJECTID = @VersionID
END
GO

-- 0.2.4 (added SWMMBuild)
IF OBJECT_ID('Framework.spModelElements_XMLBulkImport') IS NOT NULL
    DROP PROCEDURE Framework.spModelElements_XMLBulkImport;
GO
CREATE PROCEDURE Framework.spModelElements_XMLBulkImport
@ModelName varchar(200),
@VersionName varchar(60) = NULL,
@INP_xml_path varchar(7000),
@SWMMBuild varchar(20),
@DocPath varchar(max) = NULL,
@Description varchar(7000) = NULL,
@TagsCSV varchar(max) = NULL
WITH EXECUTE AS OWNER
AS
BEGIN
    SET XACT_ABORT ON
    SET NOCOUNT ON
    DECLARE @time_stamp datetime
    SET @time_stamp = GETDATE()

    DECLARE @model_id int
    SELECT @model_id = OBJECTID FROM Framework.Models WHERE ModelName = @ModelName
    
    DECLARE @is_new_model bit
    SET @is_new_model = 0
    IF @model_id IS NULL BEGIN
        INSERT INTO [Framework].[Models] (ModelName) VALUES (@Modelname)
        SET @model_id = IDENT_CURRENT('Framework.Models')
        SET @is_new_model = 1
    END

    EXEC Framework.spAddModelTags @ModelID = @model_id, @TagsCSV = @TagsCSV
    -- load the xml document into an xml variable @Elements
    DECLARE @INP xml
    DECLARE @statement nvarchar(max)
    SET @statement = 'SELECT @INP = BulkColumn FROM OPENROWSET(BULK ' + QUOTENAME(@INP_xml_path, '''') + ', SINGLE_BLOB) AS ORS'
    EXEC sp_executesql @statement, 
                       N'@INP xml OUTPUT',
                       @INP OUTPUT

    SELECT CONVERT(varchar(30), INP.ElementClass.query('local-name(.)')) AS ElementClass,
           INP.ElementClass.query('.') AS XML_ElementClass 
    INTO #XML_INP 
    FROM @INP.nodes('/INP[1]/*') AS INP(ElementClass)
    
    DECLARE XML_INP_cursor CURSOR FOR
        SELECT ElementClass, XML_ElementClass 
        FROM #XML_INP

    DECLARE @total_new_modelelements_count int
    SET @total_new_modelelements_count = 0
    DECLARE @current_new_modelelements_count int
    DECLARE @current_elclass varchar(30)
    DECLARE @Elements xml
    OPEN XML_INP_cursor
    FETCH NEXT FROM XML_INP_cursor
        INTO @current_elclass, @Elements

    WHILE @@FETCH_STATUS = 0 BEGIN
        -- get table name for the element class
        DECLARE @table_name varchar(30)
        SELECT @table_name = TABLE_NAME FROM Framework.Objects_SWMM WHERE ELEMENT_CLASS = @current_elclass

        -- populate table variable with column names and data types from the element class table, excluding columns with default values (CreatedOn, User)
        DECLARE @cols_datatypes TABLE (Ordinal int, ColumnName varchar(30), DataType varchar(30))
        DELETE FROM @cols_datatypes 
        INSERT INTO @cols_datatypes (Ordinal, ColumnName, DataType)
            SELECT ORDINAL_POSITION, 
                   COLUMN_NAME, 
                   CASE DATA_TYPE
                        WHEN 'varchar' THEN DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS varchar(5)) + ')' 
                        WHEN 'nvarchar' THEN DATA_TYPE + '(' + CAST(CHARACTER_MAXIMUM_LENGTH AS varchar(5)) + ')' 
                        ELSE DATA_TYPE
                   END
                FROM INFORMATION_SCHEMA.COLUMNS
                WHERE TABLE_CATALOG = 'SWMMR'
                      AND TABLE_SCHEMA = 'Framework'
                      AND TABLE_NAME = @table_name
                      AND COLUMN_DEFAULT IS NULL
                ORDER BY ORDINAL_POSITION

        -- populate table variable with xml element parameters that match element class table column names, and their data types
        DECLARE @parameters TABLE (Ordinal int, ParameterName varchar(30), DataType varchar(30));
        DELETE FROM @parameters;
        WITH XMLParameters AS (
            SELECT DISTINCT Elements.Parameter.value('local-name(.)', 'varchar(30)') AS ParameterName
                FROM @Elements.nodes('/*/*/*') AS Elements(Parameter)
        )
        INSERT INTO @parameters (Ordinal, ParameterName, DataType)
            SELECT CD.Ordinal, XMLParameters.ParameterName, CD.DataType 
                FROM XMLParameters INNER JOIN @cols_datatypes AS CD 
                    ON XMLParameters.ParameterName = CD.ColumnName

        -- build column defitions string out of parameters for dynamic table creation
        DECLARE @param_defs nvarchar(max)
        SET @param_defs = NULL
        SELECT @param_defs = COALESCE(@param_defs + ', ', '') + ParameterName + ' ' + DataType
            FROM @parameters
            ORDER BY Ordinal

        -- build comma separated list of column names for dynamic inserts 
        DECLARE @param_names nvarchar(max)
        SET @param_names = NULL
        SELECT @param_names = COALESCE(@param_names + ', ', '') + ParameterName
            FROM @parameters
            ORDER BY Ordinal

        -- build comma separated select list for dynamic XQuery
        DECLARE @xml_select nvarchar(max)
        SET @xml_select = NULL
        SELECT @xml_select = COALESCE(@xml_select + ', ', '') + 
       'CONVERT(' + DataType + ', NULLIF(XML_Element.value(''(' + ParameterName + ')[1]'', ''varchar(7000)''), ''NULL'')) AS ' + ParameterName
            FROM @parameters
            ORDER BY Ordinal

        -- build column equality conjunction for ON clause of dynamic join 
        DECLARE @join_on varchar(max)
        SET @join_on = NULL
        SELECT @join_on = COALESCE(@join_on + ' AND ', '') + 'ISNULL(Imported.' + ParameterName + ', '''') = ISNULL(Existing.' + ParameterName + ', '''')'
            FROM @parameters
            ORDER BY Ordinal

        -- build full transaction
        DECLARE @trans nvarchar(max)
        SET @trans = 
        '-- split up XML by element into a temp table #XML_Elements
        SELECT Elements.Element.query(''node()'') AS XML_Element INTO #XML_Elements 
        FROM @Elements.nodes(''/' + @current_elclass + '[1]/Element'') AS Elements(Element);

        -- add primary key to #XML_elements, create primary xml index on the xml column
        ALTER TABLE #XML_Elements ADD OBJECTID int IDENTITY(1,1) PRIMARY KEY;
        CREATE PRIMARY XML INDEX XIX_Primary_XML_Elements ON #XML_Elements(XML_Element);
        CREATE XML INDEX XIX_PATH_XML_Elements ON #XML_Elements(XML_Element) 
            USING XML INDEX XIX_Primary_XML_Elements FOR PATH
        CREATE XML INDEX XIX_VALUE_XML_Elements ON #XML_Elements(XML_Element) 
            USING XML INDEX XIX_Primary_XML_Elements FOR VALUE

        -- parse XML in #XML_Elements into temp table #ImportElements, where each row is an element, and each column is a parameter
        CREATE TABLE #ImportedElements (OBJECTID int IDENTITY(1,1) PRIMARY KEY, ElementID int NULL, ' + @param_defs + ');
        INSERT INTO #ImportedElements (' + @param_names + ') 
            SELECT ' + @xml_select + ' FROM #XML_Elements;  

        -- get a count of the number of elements being imported.
        DECLARE @imported_element_count int;
        SET @imported_element_count = @@ROWCOUNT;

        -- insert into the element class table all new elements in #ImportedElements 
        INSERT INTO Framework.' + @table_name + ' (' + @param_names + ') 
            SELECT ' + @param_names + ' 
            FROM 
            ( 
                SELECT ''Imported'' AS DataSource, ' + @param_names + ' FROM #ImportedElements 
                UNION ALL 
                SELECT ''Existing'' AS DataSource, ' + @param_names + ' FROM Framework.' + @table_name + '
            ) AS tmp 
            GROUP BY ' + @param_names + ' 
            HAVING COUNT(*) = 1 AND MIN(DataSource) = ''Imported'';

        -- Update the ElementID column in #ImportedElements with the appropriate OBJECTID from the element class table 
        UPDATE #ImportedElements  
        SET ElementID = Existing.OBJECTID 
        FROM #ImportedElements AS Imported LEFT OUTER JOIN Framework.' + @table_name + ' AS Existing ON ' + @join_on + '; 

        -- populate temp table #ModelElements with all existing elements in the element class for the current model
        CREATE TABLE #ModelElements (OBJECTID int IDENTITY(1,1) PRIMARY KEY, ElementID int, ElementName varchar(50), Active bit); 
        INSERT INTO #ModelElements (ElementID, ElementName, Active) 
            EXEC Framework.spGetElements @ElementClass = @current_elclass, @ModelName = @ModelName, @ActiveOnly = 1, @TimeUntil = NULL; 

        IF @current_elclass IN (SELECT ELEMENT_CLASS FROM Framework.Objects_SWMM WHERE NAME_COLUMN IS NOT NULL) BEGIN 
            -- insert entries into the ModelElements table for new elements, newer elements, and old elements that are now inactive
            WITH ImportedElements AS ( 
                SELECT ElementID AS ElementID, Name AS Name, 1 AS Active 
                FROM #ImportedElements 
            ) 
            INSERT INTO Framework.ModelElements (ModelID, ElementClass, ElementID, Active, TimeStamp) 
                SELECT @model_id, @current_elclass, ISNULL(ImportedElements.ElementID, ME.ElementID), 
                       ISNULL(ImportedElements.Active, 0), @time_stamp
                    FROM #ModelElements AS ME FULL OUTER JOIN ImportedElements 
                    ON ImportedElements.Name = ME.ElementName 
                    WHERE ME.ElementID <> ImportedElements.ElementID 
                          OR ImportedElements.ElementID IS NULL 
                          OR ME.ElementName IS NULL; 
        END;
        ELSE BEGIN
            WITH ImportedElements AS ( 
                SELECT ElementID AS ElementID, 1 AS Active 
                FROM #ImportedElements 
            ) 
            INSERT INTO Framework.ModelElements (ModelID, ElementClass, ElementID, Active, TimeStamp) 
                SELECT @model_id, @current_elclass, ImportedElements.ElementID, 1, @time_stamp
                    FROM ImportedElements LEFT OUTER JOIN #ModelElements AS ME
                    ON ImportedElements.ElementID = ME.ElementID 
                    WHERE ME.ElementID IS NULL;
        END;
       
        SET @current_new_modelelements_count = @@ROWCOUNT
        '

        EXEC sp_executesql @trans, 
         N'@ModelName varchar(200), @model_id int, @current_elclass varchar(30), 
           @Elements xml, @time_stamp datetime, @current_new_modelelements_count int OUTPUT', 
         @ModelName, @model_id, @current_elclass, @Elements, @time_stamp, @current_new_modelelements_count OUTPUT

        SET @total_new_modelelements_count = @total_new_modelelements_count + @current_new_modelelements_count
        
        FETCH NEXT FROM XML_INP_cursor
            INTO @current_elclass, @Elements
    END

    CREATE TABLE #NewlyInactiveElementClasses (ElementClass varchar(30))
    INSERT INTO #NewlyInactiveElementClasses (ElementClass) 
        SELECT DISTINCT ElementClass FROM Framework.ModelElements 
        WHERE ModelID = @model_id
              AND Active = 1
              AND ElementClass NOT IN (SELECT ElementClass FROM #XML_INP)

    CREATE TABLE #RetiringElements (ElementID int, ElementName varchar(30), Active bit)

    DECLARE NewlyInactiveClasses_cursor CURSOR FOR
        SELECT ElementClass 
        FROM #NewlyInactiveElementClasses

    DECLARE @total_deactivated_count int
    SET @total_deactivated_count = 0
    OPEN NewlyInactiveClasses_cursor
    FETCH NEXT FROM NewlyInactiveClasses_cursor
        INTO @current_elclass

    WHILE @@FETCH_STATUS = 0 BEGIN
        DELETE FROM #RetiringElements
        INSERT INTO #RetiringElements (ElementID, ElementName, Active) 
            EXEC Framework.spGetElements @ElementClass = @current_elclass, @ModelName = @ModelName, @ActiveOnly = 1, @TimeUntil = NULL 

        INSERT INTO Framework.ModelElements (ModelID, ElementClass, ElementID, Active, TimeStamp) 
            SELECT @model_id, @current_elclass, ElementID, 0, @time_stamp
            FROM #RetiringElements

        SET @total_deactivated_count = @total_deactivated_count + @@ROWCOUNT

        FETCH NEXT FROM NewlyInactiveClasses_cursor 
            INTO @current_elclass
    END

    IF @total_new_modelelements_count > 0 OR @total_deactivated_count > 0 OR @is_new_model = 1 BEGIN
        INSERT INTO Framework.ModelVersions (ModelID, TimeStamp, Name, SWMMBuild, DocPath, Description) 
        VALUES (@model_id, @time_stamp, @VersionName, @SWMMBuild, @DocPath, @Description)
    END
    ELSE BEGIN
        DECLARE @version_id int
        SELECT @version_id = Framework.ufnGetModelVersionID(@model_id, NULL)

        EXEC Framework.spUpdateModelVersion @VersionID = @version_id, @Name = @VersionName, @SWMMBuild = @SWMMBuild, 
            @DocPath = @DocPath, @Description = @Description
    END
        
END
GO

IF OBJECT_ID('Framework.spPopulate_FieldChanges') IS NOT NULL
    DROP PROCEDURE Framework.spPopulate_FieldChanges;
GO
CREATE PROCEDURE Framework.spPopulate_FieldChanges
WITH EXECUTE AS OWNER
AS
BEGIN
    DECLARE @CME_MaxID int, @CME_CurrentID int
    SELECT @CME_MaxID = MAX(OBJECTID), @CME_CurrentID = MIN(OBJECTID) FROM #Cascading_ModelElements

    WHILE @CME_CurrentID <= @CME_MaxID BEGIN
        DECLARE @ElementClass varchar(20), @newElementKey int
        DECLARE @currentElementKey int, @tableName varchar(50)
        DECLARE @PrimaryKey varchar(50), @CME_id int
        SELECT @ElementClass = ElementClass, @newElementKey = New_ElementID, @currentElementKey = Old_ElementID, @CME_id = OBJECTID
            FROM #Cascading_ModelElements 
            WHERE OBJECTID = @CME_CurrentID

        SELECT @tableName = TABLE_NAME, @PrimaryKey = PRIMARY_KEY 
            FROM Framework.Objects_SWMM 
            WHERE ELEMENT_CLASS = @ElementClass

        IF (@newElementKey IS NOT NULL AND @currentElementKey IS NOT NULL) BEGIN
            DECLARE @FieldNames TABLE (RowId int identity(1,1), FieldName varchar(50))

            DECLARE @Fields_TotalRowCount int, @Fields_CurrentRow int

            INSERT INTO @FieldNames (FieldName)
                SELECT COLUMN_NAME
                FROM information_schema.COLUMNS 
                WHERE TABLE_NAME= @tableName AND TABLE_SCHEMA='Framework' 
                ORDER BY ORDINAL_POSITION

            SELECT @Fields_CurrentRow = MIN(RowId), @Fields_TotalRowCount = MAX(RowID) FROM @FieldNames
            DECLARE @CurrentFieldName varchar(50)

            DECLARE @differing_fields_str varchar(max)
            SET @differing_fields_str = ''

            WHILE @Fields_CurrentRow <= @Fields_TotalRowCount BEGIN
                SELECT @CurrentFieldName = FieldName FROM @FieldNames WHERE RowId = @Fields_CurrentRow
                DECLARE @Row1Value sql_variant, @query nvarchar(800), @ParamDef nvarchar(100)
                SET @query = 'SELECT @InternalVariable = [' + @CurrentFieldName + '] FROM Framework.' + @tableName + 
                             ' WHERE [' + @PrimaryKey + '] = ' + CAST(@newElementKey AS varchar)
                SET @ParamDef = '@InternalVariable sql_variant output'
                exec sp_executesql @query, @ParamDef, @InternalVariable = @Row1Value output

                DECLARE @Row2Value sql_variant
                SET @query = 'SELECT @InternalVariable = [' + @CurrentFieldName + '] FROM Framework.' + @tableName + 
                             ' WHERE [' + @PrimaryKey + '] = ' + CAST(@currentElementKey AS varchar)
                exec sp_executesql @query, @ParamDef, @InternalVariable = @Row2Value output


                DECLARE @ValuesDiffer bit
                SET @ValuesDiffer = 0
                IF (((@Row1Value IS NULL) AND (NOT @Row2Value IS NULL)) OR ((NOT @Row1Value IS NULL) AND (@Row2Value IS NULL))) BEGIN
                    SET @ValuesDiffer = 1
                END
                ELSE IF (@Row1Value <> @Row2Value) BEGIN
                    SET @ValuesDiffer  = 1
                END

                DECLARE @IgnoreCurrentField int
                SET @IgnoreCurrentField = 0
                IF @CurrentFieldName IN ('createdOn', @PrimaryKey)
                    SET @IgnoreCurrentField = 1

                IF @ValuesDiffer = 1 AND @IgnoreCurrentField = 0 BEGIN
                    INSERT INTO #FieldChanges (CME_ID, NewElementID, CurrentElementID, NewValue, CurrentValue,
                        TableName, FieldName, FieldOrdinal)
                    VALUES (@CME_CurrentID, @newElementKey, @currentElementKey, CAST(@Row1Value as varchar), CAST(@Row2Value as varchar), 
                        @tableName, @CurrentFieldName, @Fields_CurrentRow)

                    IF LEN(@differing_fields_str) = 0 
                        SET @differing_fields_str = @CurrentFieldName
                    ELSE
                        SET @differing_fields_str = @differing_fields_str + ', ' + @CurrentFieldName
                END

                UPDATE #Cascading_ModelElements
                    SET FieldDifferences = @differing_fields_str
                    WHERE OBJECTID = @CME_id

                SET @Fields_CurrentRow = @Fields_CurrentRow + 1
            END

            DELETE FROM @FieldNames
        END

        SET @CME_CurrentID = @CME_CurrentID +1
    END
END
GO

-- 0.2.4 updated, added FilterWithAnd
IF OBJECT_ID('Framework.spPopulate_Cascading_ModelElements') IS NOT NULL
    DROP PROCEDURE Framework.spPopulate_Cascading_ModelElements;
GO
CREATE PROCEDURE Framework.spPopulate_Cascading_ModelElements
@ModelName varchar(200),
@TagsCSV varchar(max) = NULL,
@FilterWithAnd bit = 0
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @model_id int
    SELECT @model_id = Framework.ufnGetModelID(@ModelName)

    DECLARE @element_classes TABLE (RowID int IDENTITY(1,1) PRIMARY KEY, ElementClass varchar(30))
    INSERT INTO @element_classes (ElementClass)
        SELECT DISTINCT ElementClass 
            FROM Framework.ModelElements
            WHERE ModelID = @model_id
                  AND ElementClass NOT IN (SELECT ELEMENT_CLASS FROM Framework.Objects_SWMM WHERE NAME_COLUMN IS NULL)

    DECLARE @tag_table TABLE (Tag varchar(30))

    DECLARE @totalrows_elclasses int
    SET @totalrows_elclasses = @@ROWCOUNT
    DECLARE @currentrow_elclasses int
    SET @currentrow_elclasses = 1

    DECLARE @no_tags bit
    SET @no_tags = 0
    IF @TagsCSV IS NULL
        SET @no_tags = 1

    DECLARE @all_tags TABLE (Tag varchar(30))
    DECLARE @active_tags_exist bit
    INSERT INTO @all_tags
        EXEC Framework.spGetTags
    IF @@ROWCOUNT > 0 BEGIN
        SET @active_tags_exist = 1
    END 
    ELSE BEGIN
        SET @active_tags_exist = 0
    END

    IF @no_tags = 1 AND @active_tags_exist = 0
        INSERT INTO @tag_table (Tag)
        SELECT Tag FROM @all_tags
    ELSE 
        INSERT INTO @tag_table (Tag)
        SELECT Value FROM Framework.ufnSplitCSV(@TagsCSV, ',')

    DECLARE @nrow_tag_table int
    SELECT @nrow_tag_table = COUNT(*) FROM @tag_table

    WHILE @currentrow_elclasses <= @totalrows_elclasses BEGIN
        DECLARE @current_elclass varchar(30)
        SELECT @current_elclass = ElementClass 
            FROM @element_classes
            WHERE RowID = @currentrow_elclasses

        DECLARE @models TABLE (RowID int IDENTITY(1,1) PRIMARY KEY, ModelID int)
        DELETE FROM @models
        INSERT INTO @models (ModelID)
            SELECT DISTINCT ME.ModelID
            FROM Framework.ModelElements AS ME 
            WHERE ME.ElementClass = @current_elclass 
                  AND ME.ModelID <> @model_id

        DECLARE @maxrow_models int
        DECLARE @currentrow_models int
        SELECT @maxrow_models = MAX(RowID), @currentrow_models = MIN(RowID) FROM @models

        WHILE @currentrow_models <= @maxrow_models BEGIN 
            DECLARE @current_modelid int
            SELECT @current_modelid = ModelID FROM @models WHERE RowID = @currentrow_models 
            DECLARE @current_modelname varchar(200)
            SELECT @current_modelname = ModelName FROM Framework.Models WHERE OBJECTID = @current_modelid

            DECLARE @process_model bit 
            DECLARE @current_model_tags TABLE (Tag varchar(30))
            DELETE FROM @current_model_tags
            INSERT INTO @current_model_tags
                EXEC Framework.spGetModelTags @ModelName = @current_modelname
            
            IF @@ROWCOUNT = 0 BEGIN
                SET @process_model = 1
            END
            ELSE BEGIN
                IF @FilterWithAnd = 1 BEGIN
                    DECLARE @num_matching_tags int

                    SELECT @num_matching_tags = COUNT(*)
                    FROM @current_model_tags AS CMT INNER JOIN @tag_table AS TT ON CMT.Tag = TT.Tag

                    IF @num_matching_tags = @nrow_tag_table 
                        SET @process_model = 1
                    ELSE
                        SET @process_model = 0
                END
                ELSE BEGIN
                    SELECT * FROM @current_model_tags 
                    WHERE Tag IN (SELECT Tag FROM @tag_table)

                    IF @@ROWCOUNT > 0  BEGIN
                        SET @process_model = 1
                    END
                    ELSE BEGIN
                        SET @process_model = 0
                    END
                END
            END

            IF @process_model = 1 BEGIN
                DECLARE @transaction nvarchar(max)
                SET @transaction = 
                'CREATE TABLE #Source_ModelElements 
                    (OBJECTID int IDENTITY(1,1) PRIMARY KEY, ElementID int, ElementName varchar(60), Active bit);
                INSERT INTO #Source_ModelElements (ElementID, ElementName, Active) 
                    EXEC Framework.spGetElements @ElementClass = @current_elclass, 
                                                 @ModelName = @ModelName, 
                                                 @ActiveOnly = 1, 
                                                 @TimeUntil = NULL;
                CREATE TABLE #Current_ModelElements 
                    (OBJECTID int IDENTITY(1,1) PRIMARY KEY, ElementID int, ElementName varchar(60), Active bit);
                INSERT INTO #Current_ModelElements (ElementID, ElementName, Active) 
                    EXEC Framework.spGetElements @ElementClass = @current_elclass, 
                                                 @ModelName = @current_modelname, 
                                                 @ActiveOnly = 1, 
                                                 @TimeUntil = NULL;
                    
                WITH ClassesRow AS (
                    SELECT CLASS01, CLASS02, CLASS03, CLASS04, CLASS05, CLASS06 
                    FROM Framework.Objects_SWMM
                    WHERE ELEMENT_CLASS = @current_elclass
                )
                INSERT INTO #Cascading_ModelElements 
                    (ModelID, ModelName, ElementName, New_ElementID, Old_ElementID, ElementClass, 
                        Class01, Class02, Class03, Class04, Class05, Class06) 
                    SELECT @current_modelid, @current_modelname, SourceME.ElementName, SourceME.ElementID, CurrentME.ElementID, @current_elclass, 
                           ClassesRow.CLASS01, ClassesRow.CLASS02, ClassesRow.CLASS03, ClassesRow.CLASS04, ClassesRow.CLASS05, ClassesRow.CLASS06 
                        FROM ((#Current_ModelElements AS CurrentME 
                             INNER JOIN 
                             #Source_ModelElements AS SourceME 
                             ON CurrentME.ElementName = SourceME.ElementName) 
                             INNER JOIN 
                             ClassesRow 
                             ON 1 = 1) 
                        WHERE CurrentME.ElementID <> SourceME.ElementID'

                EXEC sp_executesql @transaction,
                                   N'@current_elclass varchar(60), @ModelName varchar(200), @current_modelname varchar(200), @current_modelid int',
                                   @current_elclass, @ModelName, @current_modelname, @current_modelid
            END

            SET @currentrow_models = @currentrow_models + 1
        END

        SET @currentrow_elclasses = @currentrow_elclasses + 1
    END
    
    EXEC Framework.spPopulate_FieldChanges
END
GO

IF OBJECT_ID('Framework.ufnTagID') IS NOT NULL
    DROP FUNCTION Framework.ufnTagID;
GO
CREATE FUNCTION Framework.ufnTagID (@Tag varchar(30))
RETURNS int
AS 
BEGIN
    DECLARE @tag_id int
    SELECT @tag_id = OBJECTID 
    FROM Framework.Tags
    WHERE Tag = @Tag
    RETURN @tag_id
END
GO

IF OBJECT_ID('Framework.spGetModelNames') IS NOT NULL
    DROP PROCEDURE Framework.spGetModelNames;
GO
CREATE PROCEDURE Framework.spGetModelNames
@ActiveOnly bit = 0
WITH EXECUTE AS OWNER
AS
BEGIN
    IF @ActiveOnly = 1
        SELECT ModelName 
        FROM Framework.Models 
        WHERE Active = 1
        ORDER BY ModelName
    ELSE
        SELECT ModelName 
        FROM Framework.Models 
        ORDER BY ModelName
END
GO

-- 0.2.4 added union-intersection support
IF OBJECT_ID('Framework.spGetAllModels') IS NOT NULL
    DROP PROCEDURE Framework.spGetAllModels;
GO
CREATE PROCEDURE Framework.spGetAllModels
@TagsCSV varchar(max) = ''
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @tag_table TABLE (TagID int)
    INSERT INTO @tag_table (TagID) 
        SELECT Framework.ufnTagID(Value) FROM Framework.ufnSplitCSV(@TagsCSV, ',')

    DECLARE @ModelsMaxMod TABLE (ModelID int, MaxModification datetime, UserName varchar(50))

    INSERT INTO @ModelsMaxMod (ModelID, MaxModification, UserName)
        SELECT DISTINCT MaxMod.ModelID, MaxMod.MaxTimeStamp, ME.[User]
            FROM (SELECT ModelID, MAX(TimeStamp) AS MaxTimeStamp FROM Framework.ModelElements GROUP BY ModelID) AS MaxMod
                INNER JOIN Framework.ModelElements AS ME 
                ON MaxMod.ModelID = ME.ModelID AND MaxMod.MaxTimeStamp = ME.TimeStamp;

    WITH MaxTimeStamps AS (
        SELECT ModelID, MAX(TimeStamp) AS MaxTS
        FROM Framework.ModelVersions
        GROUP BY ModelID
    ),
    LatestTags AS (
        SELECT ModelID, TagID, Max(AssignedOn) AS AssignedOn
        FROM Framework.ModelTags 
        GROUP BY ModelID, TagID
    ),
    LatestActiveTags AS (
        SELECT MT.ModelID, MT.TagID
        FROM 
            Framework.ModelTags AS MT INNER JOIN LatestTags AS LT
            ON MT.ModelID = LT.ModelID AND MT.TagID = LT.TagID AND MT.AssignedOn = LT.AssignedOn AND MT.Active = 1
    ),
    LatestVersions AS (
        SELECT MV.ModelID, MV.Name, MV.DocPath, MV.Description
        FROM Framework.ModelVersions AS MV INNER JOIN MaxTimeStamps AS MTS 
             ON MV.ModelID = MTS.ModelID AND MV.TimeStamp = MTS.MaxTS
    ),
    AllModelsAllTags AS (
        SELECT M.ModelName, LV.Name, LV.DocPath, M.CreatedOn, MMM.MaxModification, MMM.UserName, LV.Description,
            TagInTags = 
                CASE 
                    WHEN TT.TagID IS NULL THEN 0
                    ELSE 1
                END
        FROM (((Framework.Models AS M INNER JOIN @ModelsMaxMod AS MMM ON M.OBJECTID = MMM.ModelID)
             LEFT OUTER JOIN LatestActiveTags AS LAT ON M.OBJECTID = LAT.ModelID)
             LEFT OUTER JOIN LatestVersions AS LV ON M.OBJECTID = LV.ModelID)
             LEFT OUTER JOIN @tag_table AS TT ON LAT.TagID = TT.TagID
    )
    SELECT ModelName, Name, DocPath, CreatedOn, MaxModification, UserName, Description, 
           MAX(TagInTags) AS UnionSet, MIN(TagInTags) AS IntersectionSet
    FROM AllModelsAllTags
    GROUP BY ModelName, Name, DocPath, CreatedOn, MaxModification, UserName, Description
END
GO

IF OBJECT_ID('Framework.spGetModelElementClasses') IS NOT NULL
    DROP PROCEDURE Framework.spGetModelElementClasses;
GO
CREATE PROCEDURE Framework.spGetModelElementClasses
@ModelName varchar(200)
WITH EXECUTE AS OWNER
AS
BEGIN
    DECLARE @model_id int
    SELECT @model_id = Framework.ufnGetModelID(@ModelName)

    SELECT DISTINCT ElementClass 
    FROM Framework.ModelElements 
    WHERE ModelID = @model_id
END
GO

IF OBJECT_ID('Framework.spGetElements') IS NOT NULL
    DROP PROCEDURE Framework.spGetElements;
GO
CREATE PROCEDURE Framework.spGetElements
@ElementClass varchar(20), 
@ModelName varchar(200), 
@ActiveOnly bit,
@TimeUntil datetime = NULL
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @model_id int
    SELECT @model_id = Framework.ufnGetModelID(@ModelName)
    CREATE TABLE #MaxDateValues 
    (   
        ElementID int NOT NULL,
        ElementName varchar(50) NULL,
        Active bit NOT NULL
    )

    IF @TimeUntil IS NULL
        SET @TimeUntil = GETDATE()
    IF @ElementClass NOT IN (SELECT ELEMENT_CLASS FROM Framework.Objects_SWMM WHERE NAME_COLUMN IS NULL) BEGIN
        DECLARE @transaction nvarchar(max)
        SET @transaction = 'DECLARE @AllElements TABLE (ElementID int NOT NULL, ElementName varchar(50), ModelElementsID int, Active bit);' +
        'INSERT @AllElements (ElementID, ElementName, ModelElementsID, Active) ' +
                'SELECT SWMM_Elements.OBJECTID, SWMM_Elements.Name, ModelElements.OBJECTID, ModelElements.Active ' +
                'FROM Framework.ModelElements AS ModelElements INNER JOIN Framework.SWMM_' + @ElementClass + ' AS SWMM_Elements ' +
                     'ON ModelElements.ElementID = SWMM_Elements.OBJECTID ' +
                'WHERE ModelElements.ModelID = @model_id AND ModelElements.ElementClass = @ElementClass ' +
                    'AND ModelElements.TimeStamp <= @TimeUntil;' +

        'DECLARE @MaxModelElementIDs TABLE (ElementID int, MaxModelElementID int);' +
        'INSERT INTO @MaxModelElementIDs (ElementID, MaxModelElementID) ' +
            'SELECT ElementID, MAX(ModelElementsID) FROM @AllElements GROUP BY ElementID;' +

        'DECLARE @AllElementsMaxModelElementID TABLE (ElementID int NOT NULL, ElementName varchar(50), Active bit);' +
        'INSERT INTO @AllElementsMaxModelElementID (ElementID, ElementName, Active) ' + 
            'SELECT AllElements.ElementID, AllElements.ElementName, AllElements.Active ' +
            'FROM @AllElements AS AllElements INNER JOIN @MaxModelElementIDs AS MaxModelElementIDs ' +
                 'ON AllElements.ElementID = MaxModelElementIDs.ElementID ' +
            'WHERE AllElements.ModelElementsID = MaxModelElementIDs.MaxModelElementID;' +

        'IF @ActiveOnly = 1 DELETE FROM @AllElementsMaxModelElementID WHERE Active = 0;' +

        'INSERT INTO #MaxDateValues (ElementID, ElementName, Active) ' +
            'SELECT MAX(ElementID), ElementName, Active FROM @AllElementsMaxModelElementID GROUP BY ElementName, Active;'

        DECLARE @Parameters nvarchar(max) 
        SET @Parameters = '@model_id int, @TimeUntil datetime, @ActiveOnly bit, @ElementClass varchar(20)'
        EXEC sp_executesql @transaction,
                           @Parameters,
                           @model_id, @TimeUntil, @ActiveOnly, @ElementClass

    END
    --ELSE IF @ElementClass = 'Options' BEGIN
    ELSE BEGIN
        DECLARE @elementid int
        SELECT @elementid = ElementID
            FROM Framework.ModelElements
            WHERE ElementClass = @ElementClass AND ModelID = @model_id
                  AND TimeStamp = (SELECT MAX(TimeStamp) 
                                    FROM (SELECT * 
                                          FROM Framework.ModelElements 
                                          WHERE TimeStamp <= @TimeUntil AND 
                                                ElementClass = @ElementClass AND
                                                ModelID = @model_id) as Selection)

        IF @elementid IS NOT NULL
            INSERT INTO #MaxDateValues (ElementID, ElementName, Active)
                VALUES (@elementid, NULL, 1)
    END

    SELECT * FROM #MaxDateValues
END;
GO

-- 0.2.4
IF OBJECT_ID('Framework.spExportElementClass') IS NOT NULL
    DROP PROCEDURE Framework.spExportElementClass;
GO
CREATE PROCEDURE Framework.spExportElementClass
@ElementClass varchar(20), 
@ActiveOnly bit,
@VersionID int = NULL,
@ModelName varchar(200) = NULL, 
@TimeUntil datetime = NULL
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON
    DECLARE @table_name varchar(30)
    DECLARE @name_col varchar(30)
    SELECT @table_name = TABLE_NAME, @name_col = NAME_COLUMN 
        FROM Framework.Objects_SWMM
        WHERE ELEMENT_CLASS = @ElementClass

    DECLARE @model_id int
    IF @VersionID IS NOT NULL BEGIN
        SELECT @TimeUntil = TimeStamp, @model_id = ModelID
        FROM Framework.ModelVersions
        WHERE OBJECTID = @VersionID

        SELECT @ModelName = ModelName 
        FROM Framework.Models
        WHERE OBJECTID = @model_id
    END

    DECLARE @query nvarchar(max)
    SET @query = 'DECLARE @GetElements TABLE (ElementID int, ElementName varchar(50) NULL, Active bit);' +
    'INSERT INTO @GetElements ' + 
        'EXEC Framework.spGetElements @ElementClass, @ModelName, @ActiveOnly, @TimeUntil;' +
    'SELECT * FROM Framework.' + @table_name + ' WHERE OBJECTID IN (SELECT ElementID FROM @GetElements) '

    IF @name_col IS NOT NULL
        SET @query = @query + 'ORDER BY ' + @name_col

    DECLARE @params nvarchar(max)
    SET @params = '@ElementClass varchar(20), @ModelName varchar(200), @ActiveOnly bit, @TimeUntil datetime'

    EXEC sp_executesql @query, @params, @ElementClass, @ModelName, @ActiveOnly, @TimeUntil
END
GO

IF OBJECT_ID('Framework.spImportINPText') IS NOT NULL
    DROP PROCEDURE Framework.spImportINPText;
GO
CREATE PROCEDURE Framework.spImportINPText
@ModelName varchar(200),
@INPText ntext
WITH EXECUTE AS OWNER
AS
BEGIN
    SET XACT_ABORT ON
    DECLARE @model_id int
    SELECT @model_id = Framework.ufnGetModelID(@ModelName)
    INSERT INTO Framework.SWMM_INPBackup (ModelID, INP) VALUES (@model_id, @INPText) 
END
GO

IF OBJECT_ID('Framework.spExportLastINPBackup') IS NOT NULL
    DROP PROCEDURE Framework.spExportLastINPBackup;
GO
CREATE PROCEDURE Framework.spExportLastINPBackup
@ModelName varchar(200)
WITH EXECUTE AS OWNER
AS
BEGIN
    DECLARE @model_id int
    SELECT @model_id = Framework.ufnGetModelID(@ModelName)
    SELECT INP FROM Framework.SWMM_INPBackup 
        WHERE ModelID = @model_id AND createdOn = (SELECT MAX(createdON) FROM Framework.SWMM_INPBackup WHERE ModelID = @model_id)
END
GO

-- Cascading Model Elements SET/GET/COMMIT PROCEDURES

IF OBJECT_ID('Framework.spCME_GetModels') IS NOT NULL
    DROP PROCEDURE Framework.spCME_GetModels;
GO
CREATE PROCEDURE Framework.spCME_GetModels
WITH EXECUTE AS OWNER
AS
BEGIN
    WITH CME_ModelIDs AS (
        SELECT DISTINCT ModelID FROM #Cascading_ModelElements
    )
    SELECT M.ModelName 
    FROM CME_ModelIDS AS CME LEFT OUTER JOIN Framework.Models AS M
         ON CME.ModelID = M.OBJECTID 
END
GO

IF OBJECT_ID('Framework.spCME_Commit') IS NOT NULL
    DROP PROCEDURE Framework.spCME_Commit;
GO
CREATE PROCEDURE Framework.spCME_Commit
@CascadingModelName varchar(200)
WITH EXECUTE AS OWNER
AS
BEGIN
    SET XACT_ABORT ON
    DECLARE @total_committed int
    DECLARE @time_stamp datetime
    SET @time_stamp = GETDATE()

    DECLARE @model_id int
    SELECT @model_id = Framework.ufnGetModelID(@CascadingModelName)

    INSERT INTO Framework.ModelElements (ModelID, ElementClass, ElementID, [User], TimeStamp, Active)
        SELECT ModelID, ElementClass, New_ElementID, ORIGINAL_LOGIN(), @time_stamp, Active 
        FROM #Cascading_ModelElements 
        WHERE CommitValue = 1

    SET @total_committed = @@ROWCOUNT

    IF @total_committed > 0 BEGIN
        DECLARE @version varchar(60)
        SET @version = 'Update from ' + @CascadingModelName;

        WITH MaxModelVersionIDs AS (
            SELECT MAX(OBJECTID) AS MaxID
            FROM Framework.ModelVersions
            WHERE ModelID IN (
                SELECT ModelID 
                FROM #Cascading_ModelElements
                WHERE CommitValue = 1
            )
            GROUP BY ModelID
        ),
        MaxModelVersions AS (
            SELECT * 
            FROM Framework.ModelVersions AS MV INNER JOIN MaxModelVersionIDs
                 ON MV.OBJECTID = MaxModelVersionIDs.MaxID
        )
        INSERT INTO Framework.ModelVersions (ModelID, TimeStamp, Name, Description, SWMMBuild, DocPath)
            SELECT ModelID, @time_stamp, @version, Description, SWMMBuild, DocPath
            FROM MaxModelVersions

        DELETE FROM #Cascading_ModelElements
    END

    RETURN @total_committed
END
GO

IF OBJECT_ID('Framework.spCME_Get') IS NOT NULL
    DROP PROCEDURE Framework.spCME_Get;
GO
CREATE PROCEDURE Framework.spCME_Get
WITH EXECUTE AS OWNER
AS
BEGIN
    SELECT CME.OBJECTID, M.ModelName, CME.Class01, CME.Class02, CME.Class03, CME.Class04, CME.Class05, CME.Class06, CME.CommitValue
    FROM #Cascading_ModelElements AS CME LEFT OUTER JOIN Framework.Models AS M
         ON CME.ModelID = M.OBJECTID 
         ORDER BY M.ModelName, CME.Class01, CME.Class02, CME.Class03, CME.Class04, CME.Class05, CME.Class06
END
GO

IF OBJECT_ID('Framework.spCME_SetSubsetState') IS NOT NULL
    DROP PROCEDURE Framework.spCME_SetSubsetState;
GO
CREATE PROCEDURE Framework.spCME_SetSubsetState 
@CommitValue bit, 
@CME_ID int = NULL,
@ModelName varchar(200) = NULL,
@Class01 varchar(30) = NULL,
@Class02 varchar(30) = NULL,
@Class03 varchar(30) = NULL,
@Class04 varchar(30) = NULL,
@Class05 varchar(30) = NULL,
@Class06 varchar(30) = NULL
WITH EXECUTE AS OWNER
AS
BEGIN
    SET NOCOUNT ON

    DECLARE @update_query nvarchar(max)
    SET @update_query = 'UPDATE #Cascading_ModelElements ' +
                        'Set CommitValue = @CommitValue '

    DECLARE @query nvarchar(max)
    SET @query = @update_query

    DECLARE @Classes TABLE (RowID int IDENTITY(1,1), ClassName varchar(30) NULL)
    INSERT INTO @Classes (ClassName) VALUES (@Class01)
    INSERT INTO @Classes (ClassName) VALUES (@Class02)
    INSERT INTO @Classes (ClassName) VALUES (@Class03)
    INSERT INTO @Classes (ClassName) VALUES (@Class04)
    INSERT INTO @Classes (ClassName) VALUES (@Class05)
    INSERT INTO @Classes (ClassName) VALUES (@Class06)

    DECLARE @ClassCondition nvarchar(max)
    SELECT @ClassCondition = COALESCE(@ClassCondition + ' AND ', '') + 'Class0' + CAST(RowID AS varchar) + ' = ' + QUOTENAME(ClassName, '''')
        FROM @Classes
        WHERE ClassName IS NOT NULL

    DECLARE @model_id int
    SET @model_id = NULL
    IF @ModelName IS NOT NULL 
        SELECT @model_id = Framework.ufnGetModelID(@ModelName)

    IF @model_id IS NOT NULL OR @Class01 IS NOT NULL BEGIN
        IF @model_id IS NOT NULL AND @Class01 IS NOT NULL 
            SET @query = @query + 'WHERE ModelID = @model_id AND ' + @ClassCondition
        ELSE IF @model_id IS NOT NULL
            SET @query = @query + 'WHERE ModelID = @model_id '
        ELSE IF @Class01 IS NOT NULL
            SET @query = @query + 'WHERE ' + @ClassCondition

        EXEC sp_executesql @query,
                           N'@CommitValue bit, @model_id int',
                           @CommitValue, @model_id
    END
    ELSE IF @CME_ID IS NOT NULL BEGIN 
        SET @query = @update_query
        SET @query = @query + 'WHERE OBJECTID = ' + CAST(@CME_ID AS nvarchar)

        EXEC sp_executesql @query,
             N'@CommitValue bit',
             @CommitValue
    END
    ELSE BEGIN
        UPDATE #Cascading_ModelElements
        SET CommitValue = @CommitValue
    END
END
GO

IF OBJECT_ID('Framework.spCME_GetSubsetState') IS NOT NULL
    DROP PROCEDURE Framework.spCME_GetSubsetState;
GO
CREATE PROCEDURE Framework.spCME_GetSubsetState
@CME_ID int = NULL,
@ModelName varchar(200) = NULL,
@Class01 varchar(30) = NULL,
@Class02 varchar(30) = NULL,
@Class03 varchar(30) = NULL,
@Class04 varchar(30) = NULL,
@Class05 varchar(30) = NULL,
@Class06 varchar(30) = NULL
WITH EXECUTE AS OWNER
AS
BEGIN
    DECLARE @query nvarchar(max)

    DECLARE @Classes TABLE (RowID int IDENTITY(1,1), ClassName varchar(30) NULL)
    INSERT INTO @Classes (ClassName) VALUES (@Class01)
    INSERT INTO @Classes (ClassName) VALUES (@Class02)
    INSERT INTO @Classes (ClassName) VALUES (@Class03)
    INSERT INTO @Classes (ClassName) VALUES (@Class04)
    INSERT INTO @Classes (ClassName) VALUES (@Class05)
    INSERT INTO @Classes (ClassName) VALUES (@Class06)

    DECLARE @ClassCondition nvarchar(max)
    SELECT @ClassCondition = COALESCE(@ClassCondition + ' AND ', '') + 'Class0' + CAST(RowID AS varchar) + ' = ' + QUOTENAME(ClassName, '''')
        FROM @Classes
        WHERE ClassName IS NOT NULL

    DECLARE @NonNULLClasses nvarchar(max)
    SELECT @NonNULLClasses = COALESCE(@NonNULLClasses + ', ', '') + 'Class0' + CAST(RowID AS varchar)
        FROM @Classes
        WHERE ClassName IS NOT NULL

    DECLARE @model_id int
    SET @model_id = NULL
    IF @ModelName IS NOT NULL 
        SELECT @model_id = Framework.ufnGetModelID(@ModelName)

    IF @CME_ID IS NULL BEGIN
        SET @query = 'SELECT @commit_count = SUM(CAST(CommitValue AS int)), @total_count = COUNT(*) FROM #Cascading_ModelElements '
        IF @model_id IS NOT NULL AND @Class01 IS NOT NULL
            SET @query = @query + 'GROUP BY ModelID, ' + @NonNULLClasses + 
                        ' HAVING ModelID = @model_id AND ' + @ClassCondition

        ELSE IF @model_id IS NOT NULL
            SET @query = @query + 'GROUP BY ModelID HAVING ModelID = @model_id'
        ELSE IF @Class01 IS NOT NULL
            SET @query = @query + 'GROUP BY ' + @NonNULLClasses + ' HAVING ' + @ClassCondition

        DECLARE @commit_count int
        DECLARE @total_count int 

        EXEC sp_executesql @query,
             N'@commit_count int output, @total_count int output, @model_id varchar(200)',
             @commit_count = @commit_count output, @total_count = @total_count output, @model_id = @model_id

        IF @commit_count = @total_count 
            RETURN 1
        ELSE IF @commit_count = 0
            RETURN 0
        ELSE IF @commit_count < @total_count 
            RETURN 2
        ELSE 
            RETURN -1
    END
    ELSE BEGIN
        DECLARE @commit_value bit
        SELECT @commit_value = CommitValue 
            FROM #Cascading_ModelElements 
            WHERE OBJECTID = @CME_ID
        RETURN @commit_value
    END
END
GO

IF OBJECT_ID('Framework.spCME_GetSubset') IS NOT NULL
    DROP PROCEDURE Framework.spCME_GetSubset;
GO
CREATE PROCEDURE Framework.spCME_GetSubset
@ModelName varchar(200) = NULL,
@Class01 varchar(30) = NULL,
@Class02 varchar(30) = NULL,
@Class03 varchar(30) = NULL,
@Class04 varchar(30) = NULL,
@Class05 varchar(30) = NULL,
@Class06 varchar(30) = NULL
WITH EXECUTE AS OWNER
AS
BEGIN
    DECLARE @query nvarchar(max)
    SET @query = 'SELECT * FROM #Cascading_ModelElements '

    DECLARE @Classes TABLE (RowID int IDENTITY(1,1), ClassName varchar(30) NULL)
    INSERT INTO @Classes (ClassName) VALUES (@Class01)
    INSERT INTO @Classes (ClassName) VALUES (@Class02)
    INSERT INTO @Classes (ClassName) VALUES (@Class03)
    INSERT INTO @Classes (ClassName) VALUES (@Class04)
    INSERT INTO @Classes (ClassName) VALUES (@Class05)
    INSERT INTO @Classes (ClassName) VALUES (@Class06)

    DECLARE @ClassCondition nvarchar(max)
    SELECT @ClassCondition = COALESCE(@ClassCondition + ' AND ', '') + 'Class0' + CAST(RowID AS varchar) + ' = ' + QUOTENAME(ClassName, '''')
        FROM @Classes
        WHERE ClassName IS NOT NULL

    DECLARE @model_id int
    SET @model_id = NULL
    IF @ModelName IS NOT NULL 
        SELECT @model_id = Framework.ufnGetModelID(@ModelName)

    IF @model_id IS NOT NULL AND @ClassCondition <> '' 
        SET @query = @query + 'WHERE ModelID = @model_id AND ' + @ClassCondition
    ELSE IF @model_id IS NOT NULL
        SET @query = @query + 'WHERE ModelID = @model_id '
    ELSE IF @Class01 IS NOT NULL 
        SET @query = @query + 'WHERE ' + @ClassCondition

    EXEC sp_executesql @query, N'@model_id int', @model_id
END
GO

IF OBJECT_ID('Framework.spCME_GetFieldChanges') IS NOT NULL
    DROP PROCEDURE Framework.spCME_GetFieldChanges;
GO
CREATE PROCEDURE Framework.spCME_GetFieldChanges
@CME_ID int
WITH EXECUTE AS OWNER
AS
BEGIN
    SELECT FieldName, CurrentValue, NewValue FROM #FieldChanges WHERE CME_ID = @CME_ID
END
GO

-- spSelectModelVersionID
--    IF OBJECT_ID('Framework.spSelectModelVersionID') IS NOT NULL
--        DROP PROCEDURE Framework.spSelectModelVersionID;
--    GO
--    CREATE PROCEDURE Framework.spSelectModelVersionID
--    @ModelName varchar(200),
--    @TimeUntil datetime = NULL
--    WITH EXECUTE AS OWNER
--    AS
--    BEGIN
--        DECLARE @model_id int
--        EXEC Framework.spGetModelID @ModelName = @ModelName, @ModelID = @model_id OUTPUT
--
--        DECLARE @time_stamp datetime
--        IF @TimeUntil IS NULL 
--            SELECT @time_stamp = MAX(TimeStamp) FROM Framework.ModelVersions WHERE ModelID = @model_id
--        ELSE
--            SELECT @time_stamp = MAX(TimeStamp) FROM Framework.ModelVersion WHERE ModelID = @model_id AND TimeStamp <= @TimeUntil
--
--        SELECT OBJECTID 
--        FROM Framework.ModelVersions
--        WHERE ModelID = @model_id
--        AND TimeStamp = @time_stamp
--    END
--    GO


-- 0.2.4  TODO
-- spDropModelTags
--    IF OBJECT_ID('Framework.spDropModelTags') IS NOT NULL
--        DROP PROCEDURE Framework.spDropModelTags;
--    GO
--    CREATE PROCEDURE [Framework].[spDropModelTags]
--    @ModelName varchar(200),
--    @TagsCSV varchar(max)
--    WITH EXECUTE AS OWNER
--    AS
--    BEGIN
--        SET XACT_ABORT ON
--        SET NOCOUNT ON
--
--        DECLARE @model_id int
--        EXEC Framework.spGetModelID @ModelName = @ModelName, @ModelID = @model_id OUTPUT
--
--        DECLARE @tags TABLE (Tag varchar(200))
--        INSERT INTO @tags (Tag) 
--            SELECT Value FROM Framework.ufnSplitCSV(@TagsCSV, ',')
--                  
--        DECLARE @tag varchar(200)
--
--        DECLARE tag_cursor CURSOR FOR
--            SELECT Tag FROM @tags
--            ORDER BY Tag
--
--        OPEN tag_cursor 
--        FETCH NEXT FROM tag_cursor 
--        INTO @tag;
--
--        WHILE @@FETCH_STATUS = 0 BEGIN
--            DECLARE @tag_id int
--            SET @tag_id = NULL
--
--            EXEC Framework.spGetTagID @Tag = @tag, @TagID = @tag_id OUTPUT
--
--            IF @tag_id IS NOT NULL BEGIN
--
--                DECLARE @modeltags_id int
--                SET @modeltags_id = NULL
--                DECLARE @active int
--                SET @active = NULL
--
--                SELECT @modeltags_id = OBJECTID, @active = Active
--                FROM Framework.ModelTags
--                WHERE ModelID = @model_id AND TagID = @tag_id
--
--                IF @active = 1
--                    INSERT INTO Framework.ModelTags (ModelID, TagID, Active)
--                    VALUES (@model_id, @tag_id, 0)
--            END
--
--            FETCH NEXT FROM tag_cursor 
--            INTO @tag;
--        END
--
--        CLOSE tag_cursor;
--        DEALLOCATE tag_cursor;
--    END
--    GO

-- TODO
-- spSetModelDescription
--    IF OBJECT_ID('Framework.spSetModelDescription') IS NOT NULL
--        DROP PROCEDURE Framework.spSetModelDescription;
--    GO
--    CREATE PROCEDURE [Framework].[spSetModelDescription]
--    @ModelName varchar(200),
--    @Description varchar(7000)
--    WITH EXECUTE AS OWNER
--    AS
--    BEGIN
--        SET NOCOUNT ON
--        DECLARE @model_id int
--        EXEC Framework.spGetModelID @ModelName = @ModelName, @ModelID = @model_id OUTPUT
--
--        DECLARE @version_id int 
--        EXEC Framework.spGetModelVersionID @ModelName = @ModelName, @TimeUntil = NULL, @VersionID = @version_id OUTPUT
--
--        UPDATE Framework.ModelVersions
--        SET Description = @Description
--        WHERE OBJECTID = @version_id
--    END
--    GO

-- TODO
--spGetModelDescription
--    IF OBJECT_ID('Framework.spGetModelDescription') IS NOT NULL
--        DROP PROCEDURE Framework.spGetModelDescription;
--    GO
--    CREATE PROCEDURE [Framework].[spGetModelDescription]
--    @ModelName varchar(200),
--    @VersionID int = NULL,
--    @TimeUntil datetime = NULL
--    WITH EXECUTE AS OWNER
--    AS
--    BEGIN
--        DECLARE @model_id int
--        EXEC Framework.spGetModelID @ModelName = @ModelName, @ModelID = @model_id OUTPUT
--
--        IF @VersionID IS NULL
--            EXEC Framework.spGetModelVersionID @ModelName = @ModelName, @TimeUntil = @TimeUntil, @VersionID = @VersionID OUTPUT
--
--        SELECT [Description]
--        FROM Framework.ModelVersions
--        WHERE OBJECTID = @VersionID
--    END
--    GO

-- TODO
-- spSelectModelVersionTimeStamp
--    IF OBJECT_ID('Framework.spSelectModelVersionTimeStamp') IS NOT NULL
--        DROP PROCEDURE Framework.spSelectModelVersionTimeStamp;
--    GO
--    CREATE PROCEDURE Framework.spSelectModelVersionTimeStamp
--    @ModelName varchar(200),
--    @VersionID int = NULL,
--    @TimeUntil datetime = NULL
--    WITH EXECUTE AS OWNER
--    AS
--    BEGIN
--        DECLARE @model_id int
--        EXEC Framework.spGetModelID @ModelName = @ModelName, @ModelID = @model_id OUTPUT
--
--        IF @VersionID IS NULL 
--            EXEC Framework.spGetModelVersionID @ModelName = @ModelName, @TimeUntil = @TimeUntil, @VersionID = @VersionID OUTPUT
--
--        SELECT TimeStamp
--        FROM Framework.ModelVersions
--        WHERE OBJECTID = @VersionID
--    END
--    GO

-- 0.2.4 updated TODO
--spSelectModelVersion
--    IF OBJECT_ID('Framework.spSelectModelVersion') IS NOT NULL
--        DROP PROCEDURE Framework.spSelectModelVersion;
--    GO
--    CREATE PROCEDURE [Framework].[spSelectModelVersion]
--    @ModelName varchar(200),
--    @VersionID int = NULL,
--    @TimeUntil datetime = NULL
--    WITH EXECUTE AS OWNER
--    AS
--    BEGIN
--        DECLARE @model_id int
--        EXEC Framework.spGetModelID @ModelName = @ModelName, @ModelID = @model_id OUTPUT
--
--        IF @VersionID IS NULL 
--            EXEC Framework.spGetModelVersionID @ModelName = @ModelName, @TimeUntil = @TimeUntil, @VersionID = @VersionID OUTPUT
--
--        SELECT Name 
--        FROM Framework.ModelVersions
--        WHERE OBJECTID = @VersionID
--    END
--    GO

--# USERS

DECLARE @Principals TABLE (domain varchar(60) NULL, username varchar(60), password varchar(100) NULL)
INSERT INTO @Principals (domain, username) VALUES ('WATER', 'matthew.plourde')
INSERT INTO @Principals (domain, username) VALUES ('WATER', 'eric.d.white')
INSERT INTO @Principals (domain, username) VALUES ('WATER', 'rajesh.rajan')
INSERT INTO @Principals (domain, username) VALUES ('WATER', 'kim.artita')
INSERT INTO @Principals (domain, username) VALUES ('WATER', 'aaron.signarovitz')
INSERT INTO @Principals (domain, username) VALUES ('WATER', 'gary.martens')
INSERT INTO @Principals (domain, username) VALUES ('WATER', 'jaclyn.marge')
INSERT INTO @Principals (domain, username) VALUES ('CDM_INC', 'SPEERED')
INSERT INTO @Principals (domain, username) VALUES ('PLOURDE-T420I', 'MATTHEW')
INSERT INTO @Principals (domain, username) VALUES ('WATER', 'gary.martens')
INSERT INTO @Principals (username, password) VALUES ('modeler1', 'sunnyday')
INSERT INTO @Principals (username, password) VALUES ('modeler2', 'sunnyday')

DECLARE Principals_cursor CURSOR FOR 
    SELECT domain, username, password FROM @Principals

OPEN Principals_cursor

DECLARE @domain varchar(60)
DECLARE @username varchar(60)
DECLARE @password varchar(60)
FETCH NEXT FROM Principals_cursor
INTO @domain, @username, @password;

DECLARE @transaction nvarchar(max)
WHILE @@FETCH_STATUS = 0 BEGIN
    SET @transaction = NULL

    IF @password IS NULL AND @domain IS NOT NULL BEGIN
        DECLARE @principal varchar(120) 
        SET @principal = @domain + '\' + @username
        SET @transaction = '
        BEGIN TRY
            IF NOT EXISTS (SELECT * FROM sys.server_principals WHERE name = N''' + @principal + ''')
                CREATE LOGIN [' + @principal + '] FROM WINDOWS
            CREATE USER [' + @username + '] FOR LOGIN [' + @principal + ']
            GRANT SELECT ON SCHEMA::[Framework] TO [' + @username + '] AS [modelking]
            GRANT EXECUTE ON SCHEMA::[Framework] TO [' + @username + '] AS [modelking]
        END TRY BEGIN CATCH END CATCH'
    END
    ELSE BEGIN
        SET @transaction = '
        BEGIN TRY
            IF EXISTS (SELECT * FROM sys.server_principals WHERE name = N''' + @username + ''')
                DROP LOGIN [' + @username + ']
            CREATE LOGIN [' + @username + '] WITH PASSWORD = ''' + @password + ''', 
                DEFAULT_DATABASE = [SWMMR], 
                CHECK_EXPIRATION = OFF,
                CHECK_POLICY = OFF
            CREATE USER [' + @username + '] FOR LOGIN [' + @username + '] 
                WITH DEFAULT_SCHEMA = [Framework]
            GRANT SELECT ON SCHEMA::[Framework] TO [' + @username + '] AS [modelking]
            GRANT EXECUTE ON SCHEMA::[Framework] TO [' + @username + '] AS [modelking]
        END TRY BEGIN CATCH END CATCH'
    END

    IF @transaction IS NOT NULL BEGIN
        EXEC sp_executesql @transaction
    END

    FETCH NEXT FROM Principals_cursor
    INTO @domain, @username, @password;
END
GO
